Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Questions
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
Solved! Go to 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/
@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!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |