Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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?
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • vik0810's avatar
    vik0810
    Icon for Resolver V rankResolver V
    6 years ago

    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.
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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%.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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