How to prevent aggregation

Question & Answer
,
1

I have 2 datasets.
One of them is related to some projects in United states categorized by states, agencies and categories. each project has it owns value. so there are several rows with same state name. Other dataset contains population and land area of united states by states. I need to create a dashboard and define some KPIs like “value per capita” or “value per square ft” . When I join the 2 datasets with the primary key of state name, it aggregates the population and land area as many as the number of rows with same state name and the figures appears completely wrong. for example, Alaska has 13 projects and its population is 730000. But when I filter only on Alaska, the population amount shows the amount of 9,490,000 which is 13*730,000. I changed the aggregation type of population from SUM to AVE. This works in case I filter in only 1 state. When I filter 2 or 3 or more states it calculates the average of population where as I need to see the total of population of filtered states.

1 Like
2

Hello @Alireza_Bavafa , welcome to the QuickSight community! I think a calculation that would be super helpful in this scenario would be the avgOver function. Try replacing your population dataset field with a calculated field that would look like this:

avgOver(sum({Population}), [{State}], PRE_AGG)

This will get your average population value for the total based on the state, that way when you filter to include 2 or 3 states each row should just show the value for the specific state it is linked to. I will link the documentation for the avgOver function in case you want to dive in a little further. Let me know if this helps and good luck!

3

Hello @Alireza_Bavafa, did my response help you resolve the issue you were facing in QuickSight? If so I can mark it as a solution.

4

Hi Dylan
I tested it. It didn’t work. I apologize for my delay in answer

5

Hello @Alireza_Bavafa, do you mind posting a new question with any new information you have uncovered? We can try to resolve your issue there!

6

Hi Dylan
I unfortunately could not solve the problem yet.