Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bchager6
Super User
Super User

Question on a What If Scenario

Hello all...

 

I created a What If parameter and named it "% Price Change." It created the below table and measure:

 

% Price Change = GENERATESERIES(-50, 50, 1)
% Price Change Value = SELECTEDVALUE('% Price Change'[% Price Change], 1)
 
With the slider that's automatically created, I am trying to calculate the impact on Net Sales given % changes
in price with the below measure, and then pull that value in to a clustered column chart to compare it to original Net Sales:
 
Net Sales after % Price Increase = SUM('Item Sales'[Net Sales]) + SUM('Item Sales'[Price])
* '% Price Change'[% Price Change Value]/100
 
Net Sales and Price are columns with a fixed decimal number format that have a Sigma character in
front of them in the Fields list and the the % Price Change Value is the measure that was generated
when I created the What If parameter.
 
However, the calculation is 5k dollars lower than it should be when comparing it to a test calculation
that I perform in the source data. If relevant, the number I'm looking for is $307k and the measure is
returning $302k.
 
Does anything within the code jump out at as being incorrect?
 

 

1 ACCEPTED SOLUTION

Hi and thank you. I think I figured it out. This code looks to be working:

 

Revenue after % Price Change =
SUMX('Item Sales','Item Sales'[Net Sales])
+ SUMX('Item Sales','Item Sales'[Qty] * 'Item Sales'[Price]
* '% Price Change'[% Price Change Value]/100)

 

Date            Item   Qty    Gross Sales  Discounts  Net Sales Unit   Price
7/20/2019   1        9        $45.00         ($9.00)       $36.00      ea      $5.00
9/21/2019   2        8        $40.00         ($8.00)       $32.00      ea      $5.00

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @bchager6 ,

Can you please share some sample data and expected the result for test? It will help us clarify your scenario.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi and thank you. I think I figured it out. This code looks to be working:

 

Revenue after % Price Change =
SUMX('Item Sales','Item Sales'[Net Sales])
+ SUMX('Item Sales','Item Sales'[Qty] * 'Item Sales'[Price]
* '% Price Change'[% Price Change Value]/100)

 

Date            Item   Qty    Gross Sales  Discounts  Net Sales Unit   Price
7/20/2019   1        9        $45.00         ($9.00)       $36.00      ea      $5.00
9/21/2019   2        8        $40.00         ($8.00)       $32.00      ea      $5.00

vik0810
Resolver V
Resolver V

I think your measure is not correct. It should be

 

Net Sales after % Price Increase = SUM('Item Sales'[Net Sales]) + SUM('Item Sales'[Net Sales])
* '% Price Change'[% Price Change Value]/100
 
You must have lines in Item Sales with more than one position, therefore your value is lower, than it should be.
 

Thank you. I tried that as well and while it brings me closer, it's still a few hundred dollars off. I may be overthinking things, but I tested the measure results by going in to the source data, changing the prices by 10%, and then multiplying the new prices by the quantitites instead of just multiplying Net Sales by 10%.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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