Need Help!! How to calculate different row items

[Perezjav]

hi, 

I am trying to Calculate the difference between waves, but I am running into some issues. These waves are in rows and a text format ( not date ). I also want to select a specific wave and automatically show the "vs. prior wave" number. Thank you so much!!

[Anonymous]

@Perezjav 

can you post the expected output for more information.

[Perezjav]

Hi @Anonymous 

The expected output is "vs. Prior wave" number. It is highlighted in the picture.

Thank you

[v-rzhou-msft · Accepted solution]

Hi @Perezjav 

I build a table like yours to have a test.

Firstly, you need to transform your table by unpivot column like pranit828  replied before. (Select three wave columns)

Result:

Then build a Slicer Table.

Slicer = VALUES('Table'[Wave])

Add an Index column in Table.

Index = RANKX(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])),'Table'[Wave],,ASC)

Build a measure and build a matrix visual to achieve your goal.

vs Prior Wave = 
VAR _select =
    SELECTEDVALUE ( Slicer[Wave] )
VAR _selindex =
    CALCULATE (
        SUM ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Wave] = _select
        )
    )
VAR _current =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Index] = _selindex
        )
    )
VAR _prior =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Index] = _selindex - 1
        )
    )
RETURN
    _current - _prior

Result is as below.

Default:

Select Wave2/Wave3 in Slicer:

You can download the pbix file from this link: Need Help!! How to calculate different row items

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[pranit828]

Hi @Perezjav 

To start with unpivot all three wave column and then use the earlier function to get the previous wave data.