Forum Discussion

ninja18's avatar
ninja18
Icon for Helper I rankHelper I
10 months ago
Solved

Calculate difference and percentage change between two columns dynamically

Hi,   Can someone advise me on how to calculate difference and % between two column values in Power BI? I have attached screenshot of sample data along with PBI matrix mockup as well? Please let me...
Need Help
  • danextian's avatar
    danextian
    10 months ago

    Hi ninja18 

    Try this:

    My Measure = 
VAR _h4 =
    SELECTEDVALUE ( 'Table'[Header4] )
VAR _actual =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table'[Header4], 'Table'[H4 Sort] ),
            'Table'[Header4] = "Actual"
        )
    )
VAR _forecast =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table'[Header4], 'Table'[H4 Sort] ),
            'Table'[Header4] = "Forecast"
        )
    )
VAR _diff = _actual - _forecast
VAR _pct =
    DIVIDE ( _diff, _forecast )
RETURN
    SWITCH ( _h4, "Diff", _diff, "Perc", _pct, SUM ( 'Table'[Amount] ) )

    Please see the attached pbix.

ChielFaber's avatar
ChielFaber
Icon for Super User rankSuper User
10 months ago

First I recreated your example data in a table called tablename


Then I added the necessary measures:

Actual =
CALCULATE (
    [Amount],
    tablename[Header4] = "Actual"
)
Forecast =
CALCULATE (
    [Amount],
    tablename[Header4] = "Forecast"
)

 

Diff =
[Actual] - [Forecast]

 

Perc =
DIVIDE( [Diff], [Forecast] ) 

 

Make sure to format the Perc as a percentage.
I then added a matrix visual to the canvas and added the rows and columns:

 

Also added the measures:

 

Then I got your desired output:

Make sure to adjust the drill down to columns and then drill down to see all the headers. You might want to clean up the header1 names at the top right, but this will get you the desired layout.

Hope this is helpfull