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)