topic Re: How to calculate percentage difference across columns in a matrix? in DAX Commands and Tips

How to calculate percentage difference across columns in a matrix?

ExcitedFace — Thu, 29 Sep 2022 20:21:08 GMT

Questions

Using DAX, how do you calculate the percentage difference across columns in a Power BI matrix?
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

Week	Year	Revenue
1	2020	6800
2	2020	7300
3	2020	7350
1	2021	7260
2	2021	8300
3	2021	8360
1	2022	8300
2	2022	9100
3	2022	8900

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

Power BI Matrix Menu Options

Re: How to calculate percentage difference across columns in a matrix?

amitchandak — Fri, 30 Sep 2022 02:04:41 GMT

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Re: How to calculate percentage difference across columns in a matrix?

ExcitedFace — Fri, 30 Sep 2022 02:11:23 GMT

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?

Re: How to calculate percentage difference across columns in a matrix?

amitchandak — Fri, 30 Sep 2022 02:15:36 GMT

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/

Re: How to calculate percentage difference across columns in a matrix?

ExcitedFace — Fri, 30 Sep 2022 02:20:35 GMT

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!

Re: How to calculate percentage difference across columns in a matrix?

ExcitedFace — Fri, 30 Sep 2022 02:49:06 GMT

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