6 Replies Latest reply on Mar 9, 2016 2:41 AM by Francesco Dall'Asta

    How to compute a custom weighted performance index

    Francesco Dall'Asta

      Hi,

       

      I need to compute a special performance sales index. This is a calcualtion that basically considers percentage margin in different market according to different weights.

      The example workbook contains data for different salesmen that operate in three different market. The index would have to consider the performance in the first market (A), the performance in the second market (B) and the performance in the two market taken as a whole, . The third market (C) is not taken into account. For each member they apply a constant weight.

       

      The calculation would be something like:

       

      Performance by Salesman  =   PercentageMargin in MKT A * 0.6   +   PercentageMargin in MKT B * 0.2   + PercentageMargin in MKT A+B * 0.2

       

      The salesperson will be judged on this calculation.

       

      Any Idea on how to do that?

       

      I tried with IF  or CASE but I couldn't reach anything interesting..

       

      I attached also an Excel Sheet where I specified how the index should be computed and how it looks in the data sample.

       

      Thanks!

        • 1. Re: How to compute a custom weighted performance index
          Charlie Archer

          Hi Francesco,

           

          Have had a quick go at this and think i have achieved what you've set out for with two calculations.

           

          Weighting (this calculates the weighting for each market):

           

          IF MIN([MKT]) = "A" THEN [Percentage Margin] * 0.6

          ELSEIF MIN([MKT]) = "B" THEN [Percentage Margin] * 0.2

          END

           

          Combined weighting (this calculates the overall weighting (A+B) and adds this to the Weighting calculated above to get to the final weighting):

           

          AVG({EXCLUDE [MKT]: [Percentage Margin]} * 0.2) + [Weighting]


          Does this work for you?

           

          Thanks,
          Charlie

          1 of 1 people found this helpful
          • 2. Re: How to compute a custom weighted performance index
            Francesco Dall'Asta

            Hi Charlie,

            thanks for your answer!

             

            Your calculation is something similar to what I tried. The problem is that the computation are still dependent on filter for Markets. Even if I exclude MKT C in the filter, the calculus does not work.

             

            Also, the IF for the Weighting calculated field you produced, calculates a new percentage margin at row level. This is different from computing an aggregate percentage margin and multiplying it for a constant.

            Maybe I should post an excel sheet so you can understand how this index should be calculated?

             

            Thanks by the way,

            Francesco

            • 3. Re: How to compute a custom weighted performance index
              Charlie Archer

              Hi Francesco - yes if you could send over an example in this case that would be helpful.

              • 4. Re: How to compute a custom weighted performance index
                Francesco Dall'Asta

                Hi Charlie - OK, I attached the Excel in my original post.

                • 5. Re: How to compute a custom weighted performance index
                  Charlie Archer

                  Hi Francesco,

                   

                  After some playing around i have replicated the Excel. I'm sure there are a couple of steps that could be missed out here (and there may be an altogether more efficient way of carrying this out), but this gets to the end result.

                   

                  Thanks,

                  Charlie

                  • 6. Re: How to compute a custom weighted performance index
                    Francesco Dall'Asta

                    Hi Charlie,

                     

                    Thanks for your answer again. Sorry about that, I am quite new in the forum.. I do not see any attachment to your post.. Is there a way I should see a tableau workbook or something?

                     

                    By the way, I guess I reached a solution too, however I would be glad to see your workaround since you were so kind to work on it.

                     

                    In the meanwhile, I'll post mine, maybe somebody in the community might find it useful:

                     

                    It is possible to compute three different percentage margin at row level depending on the three constant, maybe through a parameter:

                     

                    CALC MARGIN FOR MARKET A  =  IF MKT='A' THEN MARGIN * 0,6 ELSE 0 END  (only margin measure should be rescale depending on the weight)

                     

                    CALC SALES FOR MARKET A = IF MKT ='A' THEN SALES  ELSE 0 END

                     

                    You do the same for the market B and A+B and compute the three ratio (Sum(Margin MKTA)/ Sum(Sales MKTA) in order to obtain the percentage margins.

                     

                    The index would be at the end :

                     

                    Percentage Margin MKT A + Percentage Margin MKT B + Percentage Margin MKT A+B. 

                    We do not have to insert constant multipliers in this calculation because they are already embedded in the single percentage margin for each MKT.

                     

                    Thanks,

                     

                    Francesco