Forum Discussion

Raptors2019's avatar
Raptors2019
Regular Visitor
2 years ago
Solved

Comparing different version's values within same column

Hi everyone!

 

My data set looks like this in PBI. Run_Type (Versions) in picture below goes up to 3, but in reality there's going to be many more different run_types (Versions). 

 

 

I am looking to select any two different run_types in the two independent slicers I have created and be able to easily compare the KPIs between the two run_types, as well as having a column for % difference. So, the desired matrix would look something like this. I wnt to ensure also that Run_Type A is always before Run_Type B in the matrix.

I am really stuck. Any help on this would be greatly appreciated!

 

Thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Raptors2019 ,

    I create a table as you mentioned.

    Then I add a slicer and a matrix into the visual page.

    Next I create a measure and put it into the matrix.

    Measure = 
VAR _currentType =
    MAX ( 'Table'[Run_Type] )
VAR _PreviousType =
    CALCULATE (
        MAX ( 'Table'[Run_Type] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Run_Type] < _currentType )
    )
VAR _currentDes =
    SELECTEDVALUE ( 'Table'[Destination] )
VAR _current =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Destination] = _currentDes
                && 'Table'[Run_Type] = _currentType
        )
    )
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Destination] = _currentDes
                && 'Table'[Run_Type] = _PreviousType
        )
    )
RETURN
    IF ( _previous <> BLANK (), ABS ( _current - _previous ) )

     

     

     

     

    Best Regards

    Yilong Zhou

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

3 Replies

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Hello Raptors2019 , remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

    • Raptors2019's avatar
      Raptors2019
      Regular Visitor
      2 years ago

      Thank you for the feedback! It's my first time posting so did not know! I don't feel comfortable sharing the Power Bi as it has all sensitive info. In fact, the charts I shared is all made up data I created in order to be able to post this question.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Raptors2019 ,

    I create a table as you mentioned.

    Then I add a slicer and a matrix into the visual page.

    Next I create a measure and put it into the matrix.

    Measure = 
VAR _currentType =
    MAX ( 'Table'[Run_Type] )
VAR _PreviousType =
    CALCULATE (
        MAX ( 'Table'[Run_Type] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Run_Type] < _currentType )
    )
VAR _currentDes =
    SELECTEDVALUE ( 'Table'[Destination] )
VAR _current =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Destination] = _currentDes
                && 'Table'[Run_Type] = _currentType
        )
    )
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[Total Cost] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Destination] = _currentDes
                && 'Table'[Run_Type] = _PreviousType
        )
    )
RETURN
    IF ( _previous <> BLANK (), ABS ( _current - _previous ) )

     

     

     

     

    Best Regards

    Yilong Zhou

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