Nested Aggregation

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.

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.

Thanks! Your method works!