Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

Sum of Distinct ID with multiple Distinct values

My data: Kindly help!

7 years ago

Anonymous

In that case

=
SUMX (
    CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ),
    [Amount]
)

Community Champion

7 years ago

Anonymous

Allexcept returns a table with all filters removed except for the filters on the specified columns.

So for each ID (each row) you get a filtered table containing all rows with the same ID.

Then CALCULATETABLE takes the distinct values from that Filtered Table

Here are some good REFERENCES for allexCEPT

Frequent Visitor

6 years ago

thanks so much - this explanation really helped. Using CALCULATETABLE to reduce the table to just one row per duplicate ID is great!