SUM of Price etc. based on distinct ID's

[Gjakova]

Hi there, I have been looking around the community for almost 2 hours but I can't seem to find a solution to my problem. Thought I got it, but the end result didn't match the expected value.

I have the following problem:

I want to calculate the price per Service ID. So let's say I want to calculate the revenue
Price * Amount = Revenue

My expected result:

Service ID	Revenue
1	1.000
2	50
3	5.100
4	585.000

Instead I get:

Service ID	Revenue
1	16.000
2	200
3	81.600
4	585.000

Does someone know how to take only a unique service ID in my measure?

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = SUMX(SUMMARIZE(GENERATE(VALUES(Data[Price]),VALUES(Data[Amount])),Data[Price],Data[Amount],"ABCD",min(Data[Price]),"EFGH",MIN(Data[Amount])),[ABCD]*[EFGH])

Hope this helps.

[v-shex-msft · Accepted solution]

HI @Gjakova,

It seems like your table exists lots of duplicate records, have you consider removing them before calculate them?
You can refer to Ashish_Mathur 's suggestion to summarize in expression or enter to 'query editor' to use the 'remove duplicate' feature.

Remove duplicates (Power Query) - Excel (microsoft.com)

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = SUMX(SUMMARIZE(GENERATE(VALUES(Data[Price]),VALUES(Data[Amount])),Data[Price],Data[Amount],"ABCD",min(Data[Price]),"EFGH",MIN(Data[Amount])),[ABCD]*[EFGH])

Hope this helps.