Skip to main content
cancel
Showing results for 
Search instead 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

Reply
debrewer
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

ItemTypeUnit CostUnit PriceMaximum Margin
AS11001100.45
BS12002280.45
CS23002900.45
DS24008000.45
ES25005010.45
1 ACCEPTED SOLUTION
debrewer
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])))))

  

View solution in original post

1 REPLY 1
debrewer
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])))))

  

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.