Forum Discussion

NickM12's avatar
NickM12
New Member
2 years ago
Solved

Variation between Calculated measure in matrix

Hi, I think this should be simple if you know how! 😉   I have a matrix with a calculated measure (working out the GP% (Margin/Sales)).   I need to subtract one periods GP% from the other, to see...
Need Help
  • hackcrr's avatar
    hackcrr
    2 years ago

    Hi, NickM12 

    You can change DAX to look like this:

    Previous GP% = 
CALCULATE(
    [GP%],
    FILTER(
    'AccManData',
    'AccManData'[Period]= "P4 YTD"
)
)

     

     

    Best Regards,

    hackcrr

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hackcrr's avatar
hackcrr
Memorable Member
2 years ago

Hi, NickM12 

To calculate the growth in GP% between two periods in the Power BI Matrix, you need to create a new metric to calculate the difference between the GP% of the current period and the GP% of the previous period. Assuming you have already calculated GP% (Gross Profit Percentage) as a metric, you can create a new metric to calculate the growth. You can do this:
Create a GP% metric: First, make sure you have a GP% metric. Here's an example if you don't already have one:

GP% = DIVIDE([Sum of TY Margin], [Sum of TY Sales])

Create a metric for GP% for the previous period: Next, create a metric to get the GP% for the previous period:

Previous GP% = 
CALCULATE(
    [GP%],
    PREVIOUSMONTH('YourDateTable'[Date])
)

Make sure to replace 'YourDateTable'[Date] with the actual date column in the date table. Create Growth Metric: Now create a metric to calculate GP% growth:

GP% Growth = 
[GP%] - [Previous GP%]

Make sure your date table is properly labeled as a date table in Power BI. The date column PREVIOUSMONTH used in the function should be continuous and have no gaps.
Adjust the date range functions (PREVIOUSMONTH, PREVIOUSDAY, etc.) based on the granularity of the data (monthly, daily, etc.).

 

 

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

NickM12's avatar
NickM12
New Member
2 years ago

Thanks for your response, I do not have a date table, as the periods are not specific dates, they are just 'P4 YTD' & P7. So I can't use the previous month feature.

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, NickM12 

    You can change DAX to look like this:

    Previous GP% = 
CALCULATE(
    [GP%],
    FILTER(
    'AccManData',
    'AccManData'[Period]= "P4 YTD"
)
)

     

     

    Best Regards,

    hackcrr

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NickM12's avatar
      NickM12
      New Member
      2 years ago

      Thanks for your help. I got it to work by using the above & removing the 'Columns',

       

      it would have been good to get in the first format, but I think using columns it is difficult