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
@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!
Hi @mansiish,
It seems to be a very similar use case to my blog post here:
Hope it helps!
Thanks,
Ian
