Forum Discussion
Helper IMatrix with YOY GP Margin Comparisons
Hi All,
I need to do a YoY comparison for PnL as in the picture:
The original data from excel was like this:
I unpivot the data which made all the Revenue, GP, Direct SG&A, etc go under 1 column: Category.
And I created a sum measure for all the values.
Now I have issues with the percentages. How do I show YoY comparison for GP Margin%, net profit margin% etc in the same matrix?
Please help.
Regards,
Sya
Hi, sya ;
Try it.
Measure = DIVIDE( CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=MAX('Table'[Year])&&[category]="OUP")), CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=MAX('Table'[Year])&&[category]="Revenue")))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
sya , I think calculation group can help here along with TI
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA 
v-yalanwu-msftCommunity Support
Hi, sya ;
You could unpivot in power query.
1.unpivot.
change to
2.create a measure.
Measure = IF(HASONEVALUE('Table'[date]),SUM('Table'[Value]), FORMAT( DIVIDE(CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MAX('Table'[Year]))), CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=MIN('Table'[Year]))))-1, "0.00%"))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
syaHi v-yalanwu-msft ,
Thank you for your help.
Maybe I am missing the point, but I'm looking to calculate the margin, for example using the sample you provided above, it will be:
OUP margin 2021= OUP/Revenue*100
OUP margin 2021= 7/5*100
OUP margin 2021= 140%
OUP margin 2022= 6/6*100
OUP margin 2021= 100%
So, the additional line in the Matrix after the Revenue line would be:
OUP margin | 140% | 100%
Please assist
- v-yalanwu-msft
Hi, sya ;
Try it.
Measure = DIVIDE( CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=MAX('Table'[Year])&&[category]="OUP")), CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=MAX('Table'[Year])&&[category]="Revenue")))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
