Create New Measure by filtering distinct id's

[shafeeqansar]

Hi , 

I want to create a new measure where i need to do SUM(AMOUNT) by filtering DISTINCT ID's. Can anyone help me with the DAX Expression to create new Measure in Power BI Desktop.

I have tried using the below expression but it is throwing error.

NEW_MEASURE =
CALCULATE (SUM(TEST[AMOUNT]), FILTER('TEST'[ID] IN SELECTCOLUMNS(DISTINCT('TEST'[ID],"Distinct_id"))

Error : Too many arguments were passed to the Distinct Function.

Note : The table contains duplicate ID's so need to filter data first then use SUM(AMOUNT). Ignore the TYPE column for now as we are only interested in ID & AMOUNT columns.

Sample Data : 

Table Name = TEST

ID         TYPE         AMOUNT

111        AA              10

111        BB               10

111        CC              10

222        BB               20

222        AB              20

222        CB              20

222        BA             20

333        ZZ              30

333        ZX              30

444        MN             40

444        MG             40

444        MX             40

In the above example the ID's are duplicated . So we need to first filter and get all the distinct ID's then  SUM(AMOUNT).

New Measure = SUM(AMOUNT) based on  Distinct ID's in the table

New Measure = (10 + 20 + 30 + 40 ) = 100 should be the final output .

Any kind of help is highly appreciated.

Thanks.

SA

[v-shex-msft]

Hi @shafeeqansar,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @shafeeqansar,

I'd like to suggest you take a look at the following blog about measure total to know how to use measure expression calculate with multiple aggregations:

Measure Totals, The Final Word 
Regards,
Xiaoxin Sheng

[MFelix]

Hi @shafeeqansar ,

Try the following measure:

Total Amount = 
sumx (SUMMARIZECOLUMNS('Table'[ID],'Table'[Amount]), 'Table'[Amount])