Row Context Using What If Parameter in Measure

[debrewer]

First, thank you for any feedback! 😁 The goal is to be able to use the What If Parameter to see how the Unit Price will change relative to raising the margin. The below data will be married with sales data to then project the lift in revenue based upon the price increase. What I need help with is understanding how I can calculate the lift in price based upon the What If but at the Item level context even when it is rolled up in a table/matrix. The Maximum Margin is a backstop for limting the amount that an items price can be raised. If the What If attempts to raise it too far, the DAX should hold it at the max (Example code shown below). Any assistance would be GREATLY appreciated! Thank you!

1. I first load the text xlsx to a pbix and generate the What If Parameter.
2. I then calculate a column for Margin and a Measure for the What If

Margin = (Sheet1[Unit Price] - Sheet1[Unit Cost])/Sheet1[Unit Price]

HikeM = SELECTEDVALUE(Hike[Hike])

          3.Once those are established I create a Measure to caluclate the price based upon the What If Param (HikeM)

Measure = IF (((SUM(Sheet1[Unit Price]) * ( 1 + [HikeM])) - SUM(Sheet1[Unit Cost])) / (SUM(Sheet1[Unit Price]) * ( 1 + [HikeM])) > max(Sheet1[Desired Margin]),
                max(Sheet1[Unit Cost]) / ( 1 - max(Sheet1[Desired Margin])),
                 (SUM(Sheet1[Unit Price]) * ( 1 + [HikeM])))

That works well, but I cannot roll the data up. This becomes problematic in that I need to be able to roll up the sales data catgorically. 

Test Data

Item	Type	Unit Cost	Unit Price	Maximum Margin
A	S1	100	110	0.45
B	S1	200	228	0.45
C	S2	300	290	0.45
D	S2	400	800	0.45
E	S2	500	501	0.45

[debrewer · Accepted solution]

I think I worked it out! 😅 I had tried SUMX in the past but on specific columns. I placed the entire statement into a SUMX and it appears to be working as expected! Thank you!

New Measure = SUMX(Sheet1,IF ((Sheet1[Unit Price] - Sheet1[Unit Cost])/Sheet1[Unit Price] >= Sheet1[Desired Margin],
                Sheet1[Unit Price],
                IF(((Sheet1[Unit Price] * ( 1 + [HikeM])) - Sheet1[Unit Cost]) / (Sheet1[Unit Price] * ( 1 + [HikeM])) > Sheet1[Desired Margin],
                Sheet1[Unit Cost] / ( 1 - Sheet1[Desired Margin]),
                 (Sheet1[Unit Price] * ( 1 + [HikeM])))))