DAX compare data from same table

[Anonymous]

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   Total

100   A   15

100   B   6

100   C   7

101   A   12

101   B   6

101   C   8

102   A   11

102   B   6

102   C   8

I want to have a list where I can choose 101, 102, 103 and have a table which shows

     101    102    Var

A  12       11      -1

B   6        6        0

C   8        8        0

or

     100    102    Var

A  15       11      -4

B   6        6        0

C   7        8        1

I've simplied this massively, but basically I want to be able to bring in a colum of information from the data set and compare it side by side.

I've created a seperate table which has

SS       Index

100     1

101     2

102     3

104     4

Product_total 1 = CALCULATE(SELECTEDVALUE(PRODUCT[product_name]),filter(SNAPSHOT,SNAPSHOT[Index]=1))

Product_total 2 = CALCULATE(SELECTEDVALUE(PRODUCT[product_name]),filter(SNAPSHOT,SNAPSHOT[Index]=2))

Is there any way to make [index]=Slicer_variable?

[v-shex-msft · Accepted solution]

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]

Thanks v-shex-msft, worked a treat!