Sum of Distinct Count (ID)

Hi, I am looking for help in calculating correct attainment % in cell F8 in the attached screenshot.

Currently, my sumover calc looks something like this and attainment shows 180% but it should be 160% since values for c & d are same.

Attainment % = sumOver(distinct_count(kpi),[manager, employee_name])/goal

Any suggestion would be highly appreciated. Thanks!

Do you have many dublicates? If there are only a few you could create a calculated field and map ‘d’ to ‘c’ and use this as a partition in the sumover. Just an idea, I am not completely sure that this will work.

From the data c and d are different employee names so formula is working fine as expected as you are grouping by manager and employee name . if you can give share more details on the requirement, it will help us.

Regards - San

Hi @thomask & @Sanjeeb2022 - I appreciate you looking into this.

@thomask - yes I do have many duplicates. Currently, the sumover() is working correctly at the employee name level but when we look at manager total (cell F8) it is counting duplicates.

@Sanjeeb2022 - I am looking to find a formula which gives correct calculation at manager level (Cell F8). Unique sum of IDs should be 8 instead of 9 but as you can see in cell F8 my sumover() formula is including duplicates.

@mansiish - Thanks, what is your data source, is it a relational database? Just thinking if you can use a custom sql and extract this information or not. However let me explore if you can use any calculated field to achieve the same or not. Will keep you posted.

Regards - San

1 Like

@mansiish - Did you try taking your distinct_count at the ID level. As you mentioned in your post screen shot that c and d are duplicate IDs, however didn’t mention how you are determining the duplicate as I don’t see the ID field in your snapshot. Ideally doing the distinct count at that right level should solve your problem. A bit more details around this would help to replicate. Thank you!

1 Like

Hi @mansiish,

It seems to be a very similar use case to my blog post here:

Hope it helps!

1 Like