cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Row Context Using What If Parameter in Measure

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
1 ACCEPTED SOLUTION
New Member

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])))))``````

New Member

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])))))``````