Merging two table without aggregation error


I have two data sets. One including states names, projects, program and category. There are several different project so there are several rows with the same state name. Also, I have one table including states population and area. I want to join these tables but when I apply join, the population of each state is placed into each state. for example if the Alaska population is 737000, and there are 8 rows of projects with Alaska state, I see 737000 for all of them which leads to an error in aggregation. I tested sumover(avg()) and avgOver(sum()) but they just create tables while I need a one single number Autograph visualization. Please let me know if you have any idea.

@Alireza_Bavafa can you try KPI visal?

1 Like

I tried. It does not give me the true thing.

Hi @Alireza_Bavafa,

Do you mean that your join is creating duplicate values and causing your aggregations to give incorrect results? Use case #2 in this article shows you how to use level-aware calculations to deduplicate data.