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

    What If Analysis

    Andy Dalugoda

      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
          Emanual Henry

          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
            Emanual Henry

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

            • 3. Re: What If Analysis
              Emanual Henry

              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
                Andy Dalugoda

                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
                  Emanual Henry

                  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
                    Andy Dalugoda

                    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
                      Emanual Henry

                      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
                        Andy Dalugoda

                        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

                         

                        thanks again for your help

                        • 9. Re: What If Analysis
                          Emanual Henry

                          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
                            Andy Dalugoda

                            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
                              Emanual Henry

                              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
                                Okechukwu Ossai

                                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
                                  Andy Dalugoda

                                  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
                                    Okechukwu Ossai

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