topic Re: SUM of distinct values in a group in DAX Commands and Tips

SUM of distinct values in a group

Anonymous — Thu, 05 Nov 2020 07:17:59 GMT

Hello,

I am fairly new to DAX so i am sure it would have been a common case but i am not able to find a solution.

Sample data for the problem :

I have three columns and i want the sum to based on distinct values in item and cost group.

For example in the above dataset i need cost 1,000 only once since it is for the same item but 2,000 to be counted twice as it is for different items while calculating sum.

I have used the following formula: CALCULATE(SUM(ITEM), GROUPBY( TABLE_NAME, ITEM, COST))

But this gives me sum of cost as 7,000

Any help would be appreciated.

Thanks in Advance!!

Re: SUM of distinct values in a group

Fowmy — Thu, 05 Nov 2020 07:57:56 GMT

Anonymous

You can try your measure this way, hope you have a similar number for each item.

Total = SUMX( SUMMARIZE( table5, Table5[ITEM], "A", MAX(Table5[COST])), [A] )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: SUM of distinct values in a group

amitchandak — Thu, 05 Nov 2020 08:04:48 GMT

Anonymous , Try a measure like

sumx(Values(Table[item]) , calculate(max(Table[Cost])))

Re: SUM of distinct values in a group

Raj_sharma123 — Thu, 18 Aug 2022 11:14:14 GMT

This measure works, thanks for the awesome solution.

Re: SUM of distinct values in a group

Anonymous — Wed, 31 Aug 2022 10:51:57 GMT

I was also facing same problem, and it worked for me,
Thankyou so much.

Re: SUM of distinct values in a group

kymramosrpo — Fri, 09 Dec 2022 07:16:33 GMT

Thank you for sharing the measure you tried to use! This helped me with mine, as my data model looks like this:

Account ID	Product ID	Transaction Date	CountedRowsFromAnotherTable (linked by Product ID)
ABC	Prod1	1/1/2020	100
ABC	Prod2	1/5/2020	50
DEF	Prod3	2/2/2020	20

What I wanted to get is a total of 150 for account, "ABC" for the year 2020, and it worked!

Re: SUM of distinct values in a group

sandeep_mehra — Fri, 10 Feb 2023 07:28:33 GMT

This is exactly what i needed today, I cant believe it was so simple. Thank you!