Aggregations in Drilldown

Hi,
I have a denormalized dataset for customer orders and for every order, I have attributes for a customer zip code such as NumberOfHomes, MedianIncome, etc. I want to drill down from Country->State->Zip in a Filled Map Visual but I am having an issue with correctly displaying aggregation for NumberOfHomes and MedianIncome at State/Country. At the zip code level ‘Number Of Homes’ is max(NumberOfHomes) but at the state level it should be sum of NumberOfHomes of distinct zip codes in a state. Is it possible to do so using LAC. For NumberOfHomes
I have tried various combinations for sum or sumOver but it is not working for me. Similarly, I tried median functions for medianincome field.
Sample data is
custno,amount,zipcode,state,country,numberofhomes,medianincome
xyz, 100.0,90230,CA,US,3000,58000
ABC,200.0,90230,CA,US,3000,58000
BCD,200.0,90201,CA,US,4000,50000
a12,345.0,84081,UT,US,450,43000

Hi @qsuser , for NumberOfHomes can you try using avg(numberofhomes,[zipcode]) as a calculated field and for the MedianIncome use median(medianincome) and then add the desired zipcode, state or country to the Location Field wells?

We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.”

1 Like

Hi @salim Salim, thanks for your response. I have a Filled Map and avg(numberofhomes,[zipcode]) works but median(medianincome) gives error message in chart “Please contact the QuickSight team to solve this issue”. I have sent the feedback requestId:28a177f6-ea4a-4f50-a4e7-0319279a43ac. Median also does not work if we select this option from Aggregation menu on the Tooltip.

Hi @qsuser, based on the sample data you provided, here is the screenshot of the calculation I have used:

image

Sample map based on the sample data with median calculation is shown below along with tooltip:

Can you please share a sample screenshot if you are still getting the error (based on your sample data)?

1 Like