calculate sum of the value if there are duplicates

[srivalli2345]

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

[tresesco]

May be just remove distinct from sum(), like:

sum(valueforemployee) 

[srivalli2345]

That will include duplicates in the sum. 

actually valueforemployee column is the total sum of valueforitem for a particular employee

[tresesco]

Are you keeping the duplicate values from same item?

[srivalli2345]

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

[tresesco · Accepted solution]

Try:

=Sum(Aggr(Sum(DISTINCT valueforemployee), employee, quarter))

[srivalli2345]

Thank you. this worked for me.