Answered by:
Scorecard  Nested IFs
Question

I'm making a scorecard in Power Pivot 2013 and my end users want the scores for each metric converted from a value to a color. There are different metric categories (Financial, Operations, Manufacturing) and there are individual metrics assigned to them. The different metric categories have different thresholds for each color.
How do I combine the below formulas together and assign a formula based upon the metric type? For example, when the metric category is "Financial" the formula should use the "financial" formula.
I greatly appreciate if anyone can offer guidance here, thanks :)
Lay Out
Column A  Metric Category
Column B  Metric Name
Column C  Score
Formula 1 use for Metric Category "Financials"
=IF([score]=0,"Grey", IF(AND([Score]>=0,[Score]<0.949), "Red", IF(AND([Score]>=.95,[Score]<0.979),"Yellow",IF(AND([Score]>=0.98,[Score]<=1.05),"Green", IF([Score]>1.05,"Blue","")))))
Formula 2 for Metric Category "Operations" (Staffing)
=IF([Score]=0, "Grey",IF(AND([Score]>0,[Score]<0.85),"Red",IF(AND([Score]>=0.85,[Score]<=0.95),"Yellow", IF(AND([Score]>0.95,[Score]<1),"Green", IF([Score]>1,"Blue"," ")))))
Formula 3 for Metric Category "Learning" (Threshold)
=If([Score]=0,"Grey", IF([Score]=.1,"Red",IF([Score]=.5,"Yellow",If([Score]=1,"Green", IF([Score]=1.1,"Blue"," ")))))
Johnny
Monday, March 2, 2015 2:20 AM
Answers

Hi Johnny,
I can't think of an elegant way of combining these formula.
Instead I'd suggest you take the bucketapproach: (http://blogs.msdn.com/b/analysisservices/archive/2014/06/06/bucketingvaluesindax.aspx).
Therefore you need to create a "lookuptable" that contains all the different ranges per category. No need to link this table.
In addition to the way described in the post, you need to add one additional argument in the Filter Expression: && MetricCategory=MetricCateroy (as you have different bucket definitions per MainCategory).
hth,
Imke
 Proposed as answer by Ed Price  MSFTMicrosoft employee Sunday, March 15, 2015 10:32 PM
 Marked as answer by Michael Amadi Friday, March 27, 2015 10:18 AM
Monday, March 2, 2015 8:55 AMAnswerer
All replies

Hi Johnny,
I can't think of an elegant way of combining these formula.
Instead I'd suggest you take the bucketapproach: (http://blogs.msdn.com/b/analysisservices/archive/2014/06/06/bucketingvaluesindax.aspx).
Therefore you need to create a "lookuptable" that contains all the different ranges per category. No need to link this table.
In addition to the way described in the post, you need to add one additional argument in the Filter Expression: && MetricCategory=MetricCateroy (as you have different bucket definitions per MainCategory).
hth,
Imke
 Proposed as answer by Ed Price  MSFTMicrosoft employee Sunday, March 15, 2015 10:32 PM
 Marked as answer by Michael Amadi Friday, March 27, 2015 10:18 AM
Monday, March 2, 2015 8:55 AMAnswerer 
Thanks Imke for sharing! That article is helpful and I'll give it a try tonight.
Johnny
Tuesday, March 3, 2015 3:51 AM 
Hi JCremo,
Did the link provided by Imke help to solve your problem?
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearnFriday, March 20, 2015 9:33 AM