SUM of distinct values in a group

[Anonymous]

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!!

[kymramosrpo]

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!

[Raj_sharma123]

This measure works, thanks for the awesome solution.

[amitchandak]

@Anonymous , Try a measure like

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

[sandeep_mehra]

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

[Anonymous]

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

[Fowmy · Accepted solution]

@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