# 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

• Hi Johnny,

I can't think of an elegant way of combining these formula.

Instead I'd suggest you take the bucket-approach: (http://blogs.msdn.com/b/analysisservices/archive/2014/06/06/bucketing-values-in-dax.aspx).

Therefore you need to create a "lookup-table" 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

Monday, March 2, 2015 8:55 AM

### All replies

• Hi Johnny,

I can't think of an elegant way of combining these formula.

Instead I'd suggest you take the bucket-approach: (http://blogs.msdn.com/b/analysisservices/archive/2014/06/06/bucketing-values-in-dax.aspx).

Therefore you need to create a "lookup-table" 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

Monday, March 2, 2015 8:55 AM
• 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,