locked
Scorecard - Nested IFs RRS feed

  • 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 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
    Answerer

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
    Answerer
  • 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: @nimblelearn

    Friday, March 20, 2015 9:33 AM