Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX compare data from same table

Hi, new to powerbi, still having a play!   I have adhoc snapshots of data, all brought into the same table.  This shows three snapshots, but there could be as many as 24 or 36.   SS     Prod   To...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    You can try to use below formula to calculate difference between selected column items:

    Measure =
VAR _min =
    MINX ( VALUES ( Table3[SS] ), [SS] )
VAR _max =
    MAXX ( VALUES ( Table3[SS] ), [SS] )
RETURN
    IF (
        HASONEVALUE ( Table3[SS] ),
        CALCULATE ( SUM ( Table3[Total] ), VALUES ( Table3[Prod] ) ),
        IF (
            COUNTROWS ( VALUES ( Table3[SS] ) ) = 2,
            CALCULATE ( SUM ( Table3[Total] ), Table3[SS] = _min )
                - CALCULATE ( SUM ( Table3[Total] ), Table3[SS] = _max )
        )
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

You can try to use below formula to calculate difference between selected column items:

Measure =
VAR _min =
    MINX ( VALUES ( Table3[SS] ), [SS] )
VAR _max =
    MAXX ( VALUES ( Table3[SS] ), [SS] )
RETURN
    IF (
        HASONEVALUE ( Table3[SS] ),
        CALCULATE ( SUM ( Table3[Total] ), VALUES ( Table3[Prod] ) ),
        IF (
            COUNTROWS ( VALUES ( Table3[SS] ) ) = 2,
            CALCULATE ( SUM ( Table3[Total] ), Table3[SS] = _min )
                - CALCULATE ( SUM ( Table3[Total] ), Table3[SS] = _max )
        )
    )

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks v-shex-msft, worked a treat!