locked
Thresholds - Scoring Patterns and Banding Method problems and maybe a bug too. RRS feed

  • Question

  •  

    I am trying to have an "actual" metric compared to a "target" for the same KPI and have my simple red,yellow and green "stoplights" behave the way I want.  More specifically, My "actual" is such that "decreasing is better" - that part is pretty obvious in setting up my KPI.  However, I cant figure out how to get "Band by normalized value of actual/target" to come anywhere close to what I want when I choose "descreasing is better".  If I change it to "increasing is better" then I it would appear I can tweak the thresholds to get something close.  So I abandoned the "Band by normalized value of actual/target" method.  If someone can tell me how to get it to work I am all ears.  Even point me in the right direction.

     

    So I switched to "Band by stated score (advanced)" this method seems more intuitive and straightforward.  This method involves me first creating a "variance" metric based on the difference between my actual and target metrics (I wish there was no need for this third metric but oh well).  Once this third metric (variance) is created, I can use this third metric and set the thresholds to exactly what I want them to be and they will be compared against the new metric.  Problem here is two-fold.  First, when using "decreasing is better" the "Threshold 2" indicator (one that I set for a green stoplight) appears to indicate that it is < (less than) the value you put in the box but that's not how it works.  If the variance metric is less than or equal to the value its turned green.  I suspect there's a bug here (see my data below).  THe second problem is that the thresholds can only be adjusted to two decimal points?   Why?  What if I want to tweak it to three decimal points?  That would be a common requirement for any precision measurement.  Has anyone noticed this bug with the Band by stated score (advanced)"?

     

        actual         target     variance    stoplight colour colour it should be
    9.00% 10.00% -1.00%     green yellow
    9.10% 10.00% -0.90%     yellow yellow

    Tuesday, January 15, 2008 2:13 AM

Answers

  • Hi TimW,

     

    By the sound of your requirements there should be no need to use "Band by Stated Score (Advanced)". The calculation for the banding method you're using is documented in Dashboard Designer. When you set your scoring pattern and banding method there was a graphic and text describing how the calculation is made. I'm pretty sure the calculation being made for you by PPS is correct.

     

    Some things to check:

     

    1. What is the Worst value used in the threshold calculation. Remember that the "Band by normalized value..." scoring pattern uses the distance from actual or target to worst not simply the actual or target values themselves.

    2. When you have your KPIs contained in a scorecard display the Raw Score figure on the target. This percentage is the result of the scoring pattern calculation that drives the indicator according to your threshold settings.

     

    HTH,

    Nick

     

     

    Tuesday, January 15, 2008 3:29 AM
  • Tim, what is the rollup indicator result you get when using the Normalized scoring method? Remember that Normalized scoring will not change the leaf level indicators but it will have an effect on non-leaf / objective level indicators. Try adding the target again to the scorecard and set the scoring method to Normalized, this way you can compare the two side by side.

     

    Cheers,

    Nick

    Tuesday, January 15, 2008 8:54 PM
  • Hi Tim,

     

    As detailed in this post raw scores should not be used when rolling scorecard data up; you will get incorrect results.

     

    You may find, though, that the rolled up indicator does not use the threshold settings that are configured on non-leaf / objective KPI even when using Normalized scoring. I believe the indicator used at the top level in your pictures is being driven by the internal scoring engine. It and does not use the threshold settings configured on non-leaf / objective KPIs. I assume that you're using a three-level indicator. If that is the case you'll find that if the rolled up score goes above 66.6% the indicator will turn green (or at least change colour).

     

    I am hoping that this will change in the next version and threshold settings on non-leaf and objective KPIs will be taken into account when displaying indicators.

     

    Cheers,
    Nick

    Wednesday, January 16, 2008 2:17 AM

All replies

  • Hi TimW,

     

    By the sound of your requirements there should be no need to use "Band by Stated Score (Advanced)". The calculation for the banding method you're using is documented in Dashboard Designer. When you set your scoring pattern and banding method there was a graphic and text describing how the calculation is made. I'm pretty sure the calculation being made for you by PPS is correct.

     

    Some things to check:

     

    1. What is the Worst value used in the threshold calculation. Remember that the "Band by normalized value..." scoring pattern uses the distance from actual or target to worst not simply the actual or target values themselves.

    2. When you have your KPIs contained in a scorecard display the Raw Score figure on the target. This percentage is the result of the scoring pattern calculation that drives the indicator according to your threshold settings.

     

    HTH,

    Nick

     

     

    Tuesday, January 15, 2008 3:29 AM
  •  

    Thanks Nick. I'm having a look right now and trying to map out the values.  Here's the formula for decreasing is better

     

    1 - (distance of actual to worst / distance of target to worst)

     

     

    One quick question, "what is meant by worst value?  Am I looking for the worst value in:

    1. the set of both actual and target metrics
    2. just the actuals (which I suspect is the only option that really makes sense but I dont want to assume here)
    3. just the target (which I then use as the sole worst value in the formula) or lastly,
    4. maybe I am looking for two "worst" values (which I doubt...) - one for comparing to actuals and one for comparing to targets? 

    Last thing, is distance of actual to worst and target to worst always a positive value

    Tuesday, January 15, 2008 4:37 AM
  • So I created the data in the above formula and it looks like the worst value does refer to the worst actual value - I was able to confirm that by setting the properties of the target and checking the show score check box and the scores that showed up beside my target values matched those I created with the worst actual value.   I was also able to get the band by normalized values of actual and target to work correctly...almost.  Like I mentioned in my first post, there seems to be a bug with Threshold 2.  I have it set to zero so only scores (from show score) with a value below zero should show up green for me.  However, scores with a value of zero show up green too.  My guess is that while Threshold 2 is described as evaluating less than the score value it has been implemented as less than or equal to the score value... 

     

    Thanks for the help Nick.

     

    Tuesday, January 15, 2008 7:08 AM
  • I found another problem.  I have created a scorecard using the KPI I have been discussing.  The KPI is now using decreasing is better with the band by normalized values of actual and target. 

     

    I have added a dimension below my KPI so that now the scorecard has the KPI broken down by this dimension.  I think that the value for the KPI is the normalized value based on the values of the dimensional members (Nick I read one of your explanations for this on your blog).  I have the score value displayed and it looks reasonable.  Problem is, this score value should turn the indicator green but instead the indicator is red.  Consider the scorecard below in the link below.

     

    http://picasaweb.google.ca/twebber.online/BIPICS/photo#5155792916296462146

     

    The score value is -17.8 which should be green - look at all the other score values that are negative and look at the colour of the indicator - they are green.  Only score values that are positive should be red (or yellow for small positive values).  Why is the rollup to Vacancy Rate not working as it should?

     

       
     


     
     
     
     
     
     
      
     
     
     
     
     
     
     
     
     

     

    Tuesday, January 15, 2008 7:54 PM
  • Tim, what is the rollup indicator result you get when using the Normalized scoring method? Remember that Normalized scoring will not change the leaf level indicators but it will have an effect on non-leaf / objective level indicators. Try adding the target again to the scorecard and set the scoring method to Normalized, this way you can compare the two side by side.

     

    Cheers,

    Nick

    Tuesday, January 15, 2008 8:54 PM
  • Bingo.  Changing the score type from raw back to normalized did the trick. 

     

    Here is the side by side scorecard.  The second target column uses raw scores and the first taget column uses normalized scores.

     

    http://picasaweb.google.ca/twebber.online/BIPICS/photo#5155817857171550050 

     

     

    Why would raw scores not work? The rollup indicator raw score looks good and is within the green territory based on my thresholds.  It is as if the rollup raw score is being evaluated against different thresholds than its leaf members?

     

    Tuesday, January 15, 2008 9:23 PM
  • Hi Tim,

     

    As detailed in this post raw scores should not be used when rolling scorecard data up; you will get incorrect results.

     

    You may find, though, that the rolled up indicator does not use the threshold settings that are configured on non-leaf / objective KPI even when using Normalized scoring. I believe the indicator used at the top level in your pictures is being driven by the internal scoring engine. It and does not use the threshold settings configured on non-leaf / objective KPIs. I assume that you're using a three-level indicator. If that is the case you'll find that if the rolled up score goes above 66.6% the indicator will turn green (or at least change colour).

     

    I am hoping that this will change in the next version and threshold settings on non-leaf and objective KPIs will be taken into account when displaying indicators.

     

    Cheers,
    Nick

    Wednesday, January 16, 2008 2:17 AM
  • Chris thankyou, your help is much appreciated.  I am trying fully understand how to best utilize PPS for actual and target comparisons and the resulting effect on indicators - this area of performance management is the most confusing to me so far.  I have lots of past BI experience but am new to performance management.  I am not exactly sure what a three level indicator is but I suspect that is what I used for this demo scorecard - a set of three stoplight indicators, each a different shape and colour. 

     

    Wednesday, January 16, 2008 3:24 AM
  • I'm having this problem too, and I see all the answers seem to be "this is how the normalized scoring works, by comparing both the actual and target to the worst value."  Is there a way to make it work where it just shows the percent based on actual divided by targer, and shows the indicator based on that value?
    Friday, January 27, 2012 8:58 PM
  • I just want to know how not to use the worst value when Decreasing is better... since this affects the stoplight.  Im getting frustrated here.

    Regards

    Thursday, February 16, 2012 9:19 PM
  • The decreasing is better doesn't work as you would expect. I have run into this in the past as well and it is frustrating.  What I would suggest is to change your Banding Method to 'Band by stated ccore (advanced)'.  Once you have this setup you can then apply your own formula or reference your own calculation that might already be available.  This takes the guessing out of what is actually going on and you will have the control you are looking for.

    Dan English's BI Blog

    Friday, February 17, 2012 2:58 AM
  • Thank you very much Dan after a painful path I could solve my issue more or less like you commented and reading several blogs, ideas threds etc..., I couldnt use band by stated score (advance) though ( I really cant explain why) instead I used the Band by Actual... and did the trick... Im sure you have a better solution than mine, this was the best I could do to solve it... just in case you want to see the full explanation this the link to my thread...

    http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/6d396951-d9d6-4b9e-a577-062ac10d6a85/

    Regards

    Adriana

    Wednesday, February 22, 2012 3:20 AM