Forum Discussion

Perezjav's avatar
Perezjav
Frequent Visitor
5 years ago
Solved

Need Help!! How to calculate different row items

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!!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Perezjav 

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Perezjav 

    can you post the expected output for more information.

    • Perezjav's avatar
      Perezjav
      Frequent Visitor
      5 years ago

      Hi Anonymous 

       

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

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.