Search code examples
powerbi

Comparing different version's values within same column - Power BI

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).

enter image description here

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

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

Thanks!

So, the desired matrix would look something like this. I want to ensure also that Run_Type A is always before Run_Type B in the matrix.

enter image description here

Solution

  • This can be achieved by following method.

    First thing need to consider is how to create the row header for each KPI (distance, volume etc.) for this the dataset need to be unpivot. See below example, I have only considered Distance and Total cost as an example.

    enter image description here

    Once you're done that, load that as a table and create a duplicate table with run types as below.

    enter image description here

    The relationship between these two should look like below, with and inactive relationship connected by Run Type

    enter image description here

    Now for filters, Type A should come from Table and Type B should come from the Run_type table

    There will be 3 measures as below

    Measure 1

    Run type A = 
VAR selectedval =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Run Type ] ) )
VAR selectedval2 =
    CALCULATE ( SELECTEDVALUE ( 'Run_type'[Run Type ] ) )
VAR distance =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Run Type ] = selectedval )
RETURN
    IF ( selectedval <= selectedval2, distance, BLANK () )

    Measure 2

    Run type B = 
VAR selectedval =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Run Type ] ) )
VAR selectedval2 =
    CALCULATE ( SELECTEDVALUE ( 'Run_type'[Run Type ] ) )
VAR distance =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Run Type ] = selectedval2 )
RETURN
    IF ( selectedval <= selectedval2, distance, BLANK () )

    Measure 3

    Difference = [Run type B] - [Run type A]

    Now create a Matrix with the following as fields.

    enter image description here

    Download the PBIX file from here.

    Don't forget to accept and upvote.