Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to show a KPI with total values based on below data. However , there are duplicates in the column and it is not giving me exact output. I want the sum to be 38357 using valueforemployee instead of valueperitem column. I used below functions
sum(distinct valueforemployee)
Aggr(sum(valueforemployee),employee)
employee | item | valueperitem | valueforemployee | quarter |
A | p1 | 2295 | 2295 | Q1-2019 |
A | p2 | 0 | 2295 | Q1-2019 |
A | p3 | 0 | 2295 | Q1-2019 |
A | p1 | 4140 | 4140 | Q2-2019 |
A | p1 | 3168 | 3168 | Q3-2019 |
A | p1 | 3168 | 3168 | Q4-2019 |
A | p1 | 5800 | 5800 | Q1-2020 |
A | p1 | 4000 | 4000 | Q2-2020 |
B | p1 | 1314 | 1314 | Q1-2019 |
B | p2 | 0 | 1314 | Q1-2019 |
B | p4 | 2328 | 2328 | Q2-2019 |
B | p4 | 4320 | 4320 | Q3-2019 |
B | p4 | 1824 | 1824 | Q4-2019 |
B | p4 | 3000 | 3000 | Q1-2020 |
B | p4 | 3000 | 3000 | Q2-2020 |
total - 38357 | ||||
thanks for helping in advance
May be just remove distinct from sum(), like:
sum(valueforemployee)
That will include duplicates in the sum.
actually valueforemployee column is the total sum of valueforitem for a particular employee
Are you keeping the duplicate values from same item?
yes. if they belong to different quarter.
My output should be like below one
employee | valueforemployee | quarter |
A | 2295 | Q1-2019 |
A | 4140 | Q2-2019 |
A | 3168 | Q3-2019 |
A | 3168 | Q4-2019 |
A | 5800 | Q1-2020 |
A | 4000 | Q2-2020 |
B | 1314 | Q1-2019 |
B | 2328 | Q2-2019 |
B | 4320 | Q3-2019 |
B | 1824 | Q4-2019 |
B | 3000 | Q1-2020 |
B | 3000 | Q2-2020 |
Try:
=Sum(Aggr(Sum(DISTINCT valueforemployee), employee, quarter))
Thank you. this worked for me.