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 the growth, but cannot find a way to do this, please could anyone help.

 

Many thanks

 

 

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.

8 Replies

  • 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 again,

     

    AccManData[Period]="P4 YTD"

    returns blank column, however if I change to

     

    AccManData[Period]="P7"
    then it looks correct, just need to get the P4 YTD, then i can subtract one from another.
     
    I have checked the spelling etc, and seems ok, dont know why it doesn't like the P4 YTD?
     
    • hackcrr's avatar
      hackcrr
      Memorable Member
      2 years ago

      HI, NickM12 

      If your PERIOD is only P7 and P4 YTD then you can write it like this:

      Previous GP% = 
CALCULATE(
    [GP%],
    FILTER(
    'AccManData',
    'AccManData'[Period]<> "P7"
)
)

       

      Best Regards,

      hackcrr

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Your solutions is great hackcrr and  miTutorials 
      Hi,  NickM12 
      Have you solved your problem? If so, can you share your solution here or mark the correct answer as a standard answer to help other members find it faster. Thank you very much for your kind cooperation!

       

       

      How to Get Your Question Answered Quickly

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

      Best Regards

      Jianpeng Li