Only count value once if there is a duplicate

DAX
1

Hi,
I have a table relationship where i have duplicates. I need a calculation where if it finds a duplicate it only sums up one value, the first or max value.

In the below image the “Risk ID” number 5 appears twice. When summing up the Average i need it to only count of the 10 values?

image
image738×297 20.5 KB

Only count once if it’s a duplicate.pbix (36.3 KB)

2

Hi @chad.sharpe ,

Thanks for sending through this file. You can use SUMMARIZE function to achieve this and the final result will not show duplicates.

*Total Risk Rating_new = SUMX(SUMMARIZE(Risk,Risk[Risk ID],“MaxRiskbyID”,MAX(Risk[Risk Rating])) ,[MaxRiskbyID])

image
image703×194 9.6 KB

Kind regards,
Hafiz

1 Like
3

Excellent… I tried a few iterations of Summarize but I could not get the syntext correct… This looks great.

4

Check out some of the data mentor in action on this solution

1 Like
5

I set aside an hour every day to learn different PowerBi “things”… I’ll take this one on today…

Thanks…