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 know if you need any additional information. Thank you in advance.

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.

Calculate difference and percentage change between two columns dynamically

8 Replies

ChielFaber
Super 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
ninja18
Helper I
10 months ago
Thank you Chiel !

I need one value column as I want to control what columns to show on the report. I have header1, header2, header3 and header4 as slicers. User will decide what columns should be visible, thats why I cant have multiple measures in values on the visual.
Praful_Potphode
Super User
10 months ago
Hi ninja18 ,
I have replicated it my pbix using visual calculations. you can change the percnetage calculation as per your logic
please check and let me know.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,
Praful
- ninja18
  Helper I
  10 months ago
  Thank you Praful, you are creating multiple measures, that approach wont work with show/hide column structure.
danextian
Super User
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.
actual foreast diff.pbix26 KB
- ninja18
  Helper I
  10 months ago
  Awesome danextian
  This is exactly what I want. I had to put few more columns in All() under filter since I am using denominator column from header 1 level. It worked like a charm. Thank you for this.
  
  Also, is it possible to color code the % column based on some thresholds. Lets say, if % is <20% red, 20-50% is amber and >50% is green. color code has to be applied only to % columns only.
  
  Just to give information, I have multiple % columns (actual data has more header 4 columns). Please let me know. Once again thank you !
  - ninja18
    Helper I
    10 months ago
    I was able to use the same calculation field (percentages) and assinged HEXCODES and then to the header column as new column and used it in Background color under Cell elements.