How to find the difference between the values of 2 or more elements with the same ID using DAX

0
votes

I want to find the difference between 2 elements with different values and same ID. I also want to sum all such differences of all the elements in form of a measure.enter image description here

Please note that the elements may not be arranged in an order. I don't want to create any other table either. I want to create a measure in Power BI called SUM that sums all the differences in the elements.

Thanks in Advance.

1
powerbidax

1 Answers

0
votes

Create a measure:

Difference = 
VAR Summary =
    ADDCOLUMNS (
        VALUES ( Table1[ID] ),
        "Min Value", CALCULATE ( MIN ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[ID] ) ),
        "Max Value", CALCULATE ( MAX ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[ID] ) )
    )
RETURN
    SUMX ( Summary, [Max Value] - [Min Value] )

Result:

enter image description here

How it works:

  • First, we create a virtual table ("Summary"), where for each ID we calculate its Min and Max values;
  • Then, we use SUMX to iterate the summary by IDs, calculate the difference between their Max and Min, and sum them up.