How can I calculate average of top 3 values quicksight?

Q&A
, ,
1

I have 4 weeks of data ranked from 1 to 4. I’m trying to get the average of the top 3 values in each category. When using the avgOver, the 4th ranked value is included in the calculation.

image
image717×45 3.53 KB

Calculate average of a measure over a specific period of time
2

Hello @itsanant , welcome to the Quicksight community!

Could you clarify what you are trying to do here? I’m also not sure how this table is structured. Is each duo of UPH and AvgOver fields marking 1 week?

Have you tried to make a calculated field and are unsure how to get the function to exclude the last week, or last duo of UPH and AvgOVer?

3

Hi duncan, each duo is one week, and I’m looking at the past 4 weeks of data and need to return the average of the top 3 UPH in this 4 week period .
The UPH is the fixed value and the AvgOver is the average of UPH.
However, the calculation is including all 4 values. In the screenshot above, I only need the average of the Top 3 UPH values so the average should be (83+94+115)/3 = 97.3, but its returning (82+83+94+115)/4 = 93.5 (or rounded to 94). How do I exclude the lowest value in this case?

4

Hello @itsanant !

Thank you for the clarification! If you have date field you can use lag:

You might have to truncate your date to weeks depending on how your dataset: