Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ExcitedFace
New Member

How to calculate percentage difference across columns in a matrix?

Questions

  1. Using DAX, how do you calculate the percentage difference across columns in a Power BI matrix?
  2. Can DAX do this without hardcoding the column value?

Use Case

For example, say, I want to create a matrix of Sum of Revenue against Week as Rows and Year as columns in a matrix table.  See Example Data below.  I want to compute percentage difference of Sum of Revenue by Year (column) for each Week (row).  See Expected Result below.  How could I compute that percentage difference without hardcoding Year?


My data source always gives year-to-date (YTD) data for the last 3 years of data.  And I am hoping to avoid having to update the hardcoded year when the new year roles over.  See Background below if interested in seeing how this is done using a PivotTable in Excel.

Example Data

WeekYearRevenue
120206800
220207300
320207350
120217260
220218300
320218360
120228300
220229100
320228900


Expected Result

Week  2020  2021  2022
1 6.76%  14.33%
2 13.70%  9.64%
3 13.74%  6.46%
Total 11.52%  9.95%


Background

I am an Excel user familiar with PivotTables.  To do the same, I would pivot Sum of Revenue on Week as Rows and Year as Columns.  Then change the field values of Sum of Revenue to Show Values As to % Difference From for Year as the base field and (previous) as the base item.  I am trying to translate this logic in an Excel PivotTable to a Power BI matrix and am getting stuck.  It appears that Power BI doesn't have the equivalent menu options.  So I am guessing I have to use DAX to get this done.

Excel PivotTable Menu Options

ExcitedFace_0-1664480172703.png


Power BI Matrix Menu Options

ExcitedFace_1-1664481018523.png

1 ACCEPTED SOLUTION

@ExcitedFace , Feature shown, not released yet offset.  We have ait for that. Till that time, we have to use time intelligence

 

https://data-marc.com/2022/09/21/how-offset-in-dax-will-make-your-life-easier/

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@ExcitedFace , In case you want Week vs last year same weekk difference.

 

First make sure you have Date/week table, with Year, week and year week

 

This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

 

diff % = divide([This week] -[Last year same week ] , [Last year same week ])

 

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Thanks @amitchandak!

Do you know if a Power BI matrix can compute the percentage difference based same row and previous column?  Similar to Excel PivotTable?

@ExcitedFace , Feature shown, not released yet offset.  We have ait for that. Till that time, we have to use time intelligence

 

https://data-marc.com/2022/09/21/how-offset-in-dax-will-make-your-life-easier/

@amitchandak, after reading about OFFSET more, I want to OFFSET by column instead of row.  Do you know if that is possible with Power BI matrix?

Links

For anyone looking at this later, here are amitchandak's link on upcoming OFFSET function plus a few more.  There are not a lot of links as of this reply.

https://data-marc.com/2022/09/21/how-offset-in-dax-will-make-your-life-easier/
https://www.linkedin.com/pulse/attempt-use-new-offset-function-dax-find-previousnext-halil-gungormus...
https://www.youtube.com/watch?v=9bE_tnX3_s8 - in Spanish (I think)
https://sandiellyortega.com/una-funcion-escondida-en-power-bi-conoce-la-funcion-offset-en-power-bi/ - link from YouTuber above - also in Spanish

Thanks, @amitchandak!  Honestly, this answers my question.  And now I know why I couldn't get DAX to do what Excel PivotTable was doing.  Thank you, again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors