14 Replies Latest reply on Apr 11, 2018 2:43 PM by Okechukwu Ossai

# What If Analysis

Hello All,

i've read a bunch of threads about what if analysis and was able to create something close to what i was originally looking for but now i have come to a point i can't find answers online

i have a list of products our we offer,  product performance and number of units

ideally, what i would like to do is give the end user the ability to change number of units based on performance

for instance reduce the unit count for "PD" from 122  to lets say 100 and increase the unit count of "CC" by 22. "CC" would go from 3 to 25 (3+22)

then i'm thinking of creating a revenue model based on the new quantity configurations

I have created parameters that lets me input quantity values but the issue i'm running into is when i increase the quantity for "CC" by 10 then it multiplies 3*10 rather than 3+10

new quantity should be 13 not 30

i've attached a sample book for your reference and any help greatly appreciated

thanks,

• ###### 1. Re: What If Analysis

Parameters will work.  Create a parameter for each value you wish the user to change and then incorporate those parameters in your calculations.

• ###### 2. Re: What If Analysis

Sorry, I didn't finish reading your post.  I'll look at the workbook.

• ###### 3. Re: What If Analysis

You have 3 rows of data.  So when you change CC = 1, Units Recommendation = 1 but Sum(Units Recommendation) = 3.  Can you do Max(Units Recommendation)?

• ###### 4. Re: What If Analysis

thanks for the quick response

max units recommended works for "CC". if i increase the unit count to 6 it shows up as 6

I also want CON to say 53, CS to say 2 etc

• ###### 5. Re: What If Analysis

Modify your Units Recommendation calculation to do all your aggregates inside.  So for CC you can do max(CC) and for CON you can do count(performance ratio) etc.

• ###### 6. Re: What If Analysis

This is my formula, how would you recommend i modify this?

if i modify this to say Max([Performance Ratio]) or Count ([Performance Ratio]) then i get an error saying i can't mix aggregate and non aggregate results

CASE [Product Category]

WHEN "CC" THEN IF [cc] = 0 THEN ([Performance Ratio]) ELSE [cc] END

WHEN "CON" THEN IF [con] = 0 THEN ([Performance Ratio]) ELSE [con] END

WHEN "CS" THEN IF [cs] = 0 THEN ([Performance Ratio]) ELSE [cs] END

WHEN "PD" THEN IF [pd] = 0 THEN ([Performance Ratio]) ELSE [pd] END

WHEN "RR" THEN IF [rr] = 0 THEN ([Performance Ratio]) ELSE [rr] END

END

Thanks so much for your help again, really appreciate it

• ###### 7. Re: What If Analysis

What are your rules?  I don't know any of the business logic so i couldn't write out any formula.

• ###### 8. Re: What If Analysis

i'm not sure if i understand what you are asking

my goal is to give the end user the ability to change the unit count based on how they think is best. there aren't set rules, the person using the dashboard will decide how to change the unit configurations

for instance, product category "PD" has low performance but has a large number of units. so the end user might decide to lower the units for "PD" from 122 to 100 and increase the unit count for "CC" by 22.

they would find the parameter named "PD" and input 100 and change the parameter for CC to 25 (3+22). all other parameters would stay at 0. so the final unit count for "CON" will stay the same at 53

below is a sample excel table with the final output im looking for

• ###### 9. Re: What If Analysis

I don't understand what the values should be when you change them.  So PD, it looks like it is 122 (122 rows).  You want to overwrite that number with whatever is in the parameter for pd?  Or do you want to add that pd parameter to 122?

• ###### 10. Re: What If Analysis

yes to overwrite the number

second column i want the 122 but the 4th column i want 100 for PD

for CC 3 on the second column and 25 on the 4th others no change

thanks,

• ###### 11. Re: What If Analysis

So how about this?  I only did it for the first parameter (CC).  If that works, then duplicate it for the other 3 parameters.

• ###### 12. Re: What If Analysis

It depends on what you want to do with the numbers. From your explanation it appears the numbers will be used as inputs to a revenue model. Since I don't have the model, I won't know how a particular solution will work. So I have two suggestions.

First Approach

This is a simple fix. Leave all your fields as they are but change the aggregation of [Units Recommendation] field from SUM() to AVG(). That's it. The problem is solved. However, I'm not sure if this will give you the correct revenue model.

Second Approach.

Only use this approach if the first approach didn't produce the desired model results. Or if you intend to use the individual measures as variables in the revenue model. What I have done here is to create separate calculated fields for the measures. I assumed the revenue model is for product categories. So, used LOD expressions fixed on Product Category.

[Count of Performance Ratio]

{FIXED [Product Category]: COUNT([Performance Ratio])}

[Avg. Performance Ratio]

{FIXED [Product Category]: AVG([Performance Ratio])}

[Units Recommendation v2]

{FIXED [Product Category]: AVG(CASE [Product Category]

WHEN "CC" THEN IF [cc] = 0 THEN ([Performance Ratio]) ELSE [cc] END

WHEN "CON" THEN IF [con] = 0 THEN ([Performance Ratio]) ELSE [con] END

WHEN "CS" THEN IF [cs] = 0 THEN ([Performance Ratio]) ELSE [cs] END

WHEN "PD" THEN IF [pd] = 0 THEN ([Performance Ratio]) ELSE [pd] END

WHEN "RR" THEN IF [rr] = 0 THEN ([Performance Ratio]) ELSE [rr] END

END)}

See attached workbook. Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 13. Re: What If Analysis

oh wow!! its like you read my mind

this is exactly what i needed, i ended up using the second method.

thank you so much and thanks Emanual for your feedback as well.

• ###### 14. Re: What If Analysis

You're welcome. I'm glad it helped.