Nested Aggregation

Q&A
,
1

Hi Quicksight Team,

I have a table with sales number in store level (Please see the following graph), what I want to do is

  1. Get the P50 sales in City level (P50 sales for all stores in that city)
  2. Then get the average sales of those P50 numbers in each State (Average those P50 sales for all cities in that State)

Is there any way to do data aggregation from original data table to the final table as shown in the following graph.

Screen Shot 2022-02-21 at 2.54.21 PM
Screen Shot 2022-02-21 at 2.54.21 PM1920×906 162 KB

2

You should be able to achieve this by calculating the median in the PRE_AGG or PRE_FILTER calculation level and then applying a simple AVG() on top of that, as follows:

avg(
  percentileDiscOver({Sales}, 50, [{City},{State}], PRE_AGG)
)

Then, you would need to put this field in the values field well of a table whereas Country and State go in the Group By field well of the table.

3

Thanks! Your method works!

4

hi @darcoli , i realised that the answer is incorrect for such nested aggregation.
Avg is applied on all the rows of the original table, rather than the shorter aggregated table.
In the example above, all the stores will have the same P50 sales for the same city. The final average sales of the P50 by state, will be skewed by cities with many stores.

I realised that Quicksight is unable to give accurate answer for nested aggregations. It will require that the output table from the first aggregation be saved as a table first, before applying the second aggregation.

Is there any way to get around this?

Thanks.