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,
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
