Forum Discussion

sya's avatar
sya
Helper I
4 years ago
Solved

Matrix 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 g...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

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.

sya's avatar
sya
Helper I
4 years ago

Hi 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