# SumOver Distinct Values

Hello, I’ve been trying to get a calculated field which sum the total_leader_budget. The total_leader_budget is unique per leader and year combination. I’d like to get a calculated field which totals these values. For instance, the new calculated field total_year should be: 15250 for this table. I want to use this new calculated field in KPI and tables. Any suggestion? I’ve been struggling with this for one day. Many thanks!

I’d like to get something like this:

Hello, So far, I’ve been able to get a calculated value which aggregates the values in the table. But when totalling values, only shows the min value, that is 12000. It does not show the total from the values. This is code for calculated value.

In addition, when creating a sumOver on top the above calculated field, only works with the table. When using a KPI, only shows the min value. Any idea?
Thanks

12000 7461432
12000 7461432 12000
600000 7461432
600000 7461432 600000
1812000 7461432
1812000 7461432 1812000
1920000 7461432
1920000 7461432 1920000
2457432 7461432
2457432 7461432 2457432

First create a calculated field for the year like this:
year = truncate(‘YYYY’, {budget_period})

Looking at the data, it’s clear that there are duplicates but you want your sum to be 100 + 250 + 12000 + 1000 + 1900 = 15250.

First you need a way to exclude the duplicates:

This is basically telling Q to take the min of {total_leader_budget_year} for every combination of {leader} and {year}.

Then wrap this min in a sum to get your final result:

2 Likes

Hi David, I really appreciate your help, you saved my day. I created the new calculated value following your calculation and worked. This is also working with the KPI. Many thanks!

Leader1 1/1/23 0:00 2023 100 100
Leader1 2/1/23 0:00 2023 100 100
Leader1 3/1/23 0:00 2023 100 100
Leader1 4/1/23 0:00 2023 100 100
Leader1 5/1/23 0:00 2023 100 100
Leader3 1/1/23 0:00 2023 250 250
Leader3 2/1/23 0:00 2023 250 250
Leader3 3/1/23 0:00 2023 250 250
Leader3 4/1/23 0:00 2023 250 250
Leader3 5/1/23 0:00 2023 250 250
Leader4 1/1/23 0:00 2023 12000 12000
Leader4 2/1/23 0:00 2023 12000 12000
Leader4 3/1/23 0:00 2023 12000 12000
Leader4 4/1/23 0:00 2023 12000 12000
Leader4 5/1/23 0:00 2023 12000 12000
Leader5 1/1/23 0:00 2023 1000 1000
Leader5 2/1/23 0:00 2023 1000 1000
Leader5 3/1/23 0:00 2023 1000 1000
Leader5 4/1/23 0:00 2023 1000 1000
Leader5 5/1/23 0:00 2023 1000 1000
Totals 13350
3 Likes

Way to go @David_Wong! Thanks for letting us know this worked for you @yellr