DAX Margin Calculation for Entire Quarter

DAX DAX Calculations
1

Hi Forum,

Got tasked with a pretty straightforward Margin analysis model. I have one more DAX measure(s) left to complete the model. I have attached the Excel file where this model initially resided along with the .pbix file. The mix formula is essentially what I need help with here which subtracts the current quarters mix with the prior quarter and multiplies that by the prior margin for that specific product then adds the same current quarter less the prior multiplied by the overall margin for the prior quarter. The DAX measure that would calculate the overall Margin for the entire quarter to multiply into the above equation is where I’m struggling with. Is there a DAX measure that I can get help with to achieve this? Thank you.
CI Margin and Mix demo.xlsx (897 KB)
CI - MarginMix v4.pbix (945.0 KB)

2

Bumping this post for more visibility.

3

Hi @korbina

Based on Details, to me it seems you are trying to get values of Last Quarter in the current quarter and then perform some other calculations. For getting previous quarter value ,try using below DAX expression. Below will provide Margin% of last quarter in current one.

Margin % LQ = CALCULATE([Margin %],all('Date'[Date]),DATEADD('Date'[Date],-1,QUARTER))

If requirements are different, can you provide a simplified excel with few sample data and expected values.

Thanks
Ankit J

4

Hello @korbina, good to see that you are having progress with your inquiry. Did the response from @ankit help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

Hi @korbina, we’ve noticed that no response has been received from you since Aug 28. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Enterprise DNA - My apologies, I have been away on vacation since last week. I will try the solution provided by Ankit today and provide feedback. Thanks again!

7

Hi Ankit -

Unfortunately, this solution was unable to resolve my issue. While the DAX formula you provided gets me to the prior quarter, using that same measure on every level of the GL Desc2 row does not provide the answer I need. For example, with the attached Excel spreadsheet, on the ‘Formula explained’ tab, the ‘PQ Mix’ highlighed in red ought to be the overall quarter (i.e. 61.8% for Q3) on each row level for GL Desc2. Also, for more clarification, if you look at the same Excel spreadsheet I sent, on the ‘summary’ tab, the formula in column W is taking the difference of the mix for that specific product and multiplying it to its own LQ margin and then it’s take the same mix for the product and multiplying it by the overall LQ margin (61.8%) for the entire portfolio. Each successive cell is being multiplied by this overall LQ % (61.8%). How can I create a DAX measure that would lock that 61.8% across each row and remove all context to that row? Thank you!
CI Margin and Mix demo.xlsx (897.0 KB)

8

Hi @korbina - Again speculating here. For getting overall Margin on each row, can try below formula

Overall Margin =
VAR Ship =
    CALCULATE (
        SUM ( MarginMix[Shipments Ext DSP] ),
        ALLSELECTED ( 'Distinct PVS'[GL Desc2] )
   )
VAR cost =
    CALCULATE (
        SUM ( MarginMix[Shipments Ext Cost] ),
        ALLSELECTED ( 'Distinct PVS'[GL Desc2] )
    )
RETURN
    DIVIDE ( ship - cost, ship )

image

Also, this Post is marked as closed. If there is any other query, please raise a new Post.

Thanks
Ankit J

9

Hi @ankit,

Thank you. The solution worked. I appreciate all your help with this.

1 Like