locked
Percentage Field KPI Variance Calculation/Stoplight Issue RRS feed

  • Question

  • In Performance Point Dashboard, the KPI variance calculation doesn’t seem to work if we are using the custom MDX to get the Actual field and then format it with  % formatting.

     

    For example, in Actual field:

     

    Scenario 1:  If we put this custom MDX formula, the variance/stoplight would be calculated properly:

    Format( ([Measures].[Measure Type Value], [Scenario].[Scenario Key].[Actual]), "#,##0.00;-#,##0.00" )

     

    Scenario 2:  If we put this custom MDX formula, the variance/stoplight would NOT be calculated properly:

    Format( ([Measures].[Measure Type Value], [Scenario].[Scenario Key].[Actual]), "#,##0%;-#,##0%" )

     

    The real formula is a bit more complicated, and that’s why we need to use the custom MDX formula in the KPI. However, the above simplified formula illustrates the issue.

     

    Has anyone ever encountered this issue? And do you have a work-around for it?

     

    Thanks,

    Sri

     

    Wednesday, August 19, 2009 9:10 PM

All replies

  • Has anyone ever seen this issue? Thanks!
    Tuesday, August 25, 2009 2:35 PM
  • Why not stick with the option that works and then utilize the Number Format capabilities within the KPI Actual and Target setup to get the percentage format output that you are looking for?


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Tuesday, August 25, 2009 6:10 PM
  • Because the KPI is applied to several fields within the same scorecard, and some of the fields have a different formatting requirement. We couldn't utilize the formatting from the cube either due to some other dashboard requirements.

    Thanks.
    Tuesday, August 25, 2009 6:48 PM
  • Don't do the formatting in the MDX, do it with the Number Format option when setting up the Actual and Target values when creating the KPI in Dashboard Designer.  Is this what you are currently doing?  It looked like you were applying formatting in the custom MDX.

    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Tuesday, August 25, 2009 6:57 PM
  • We couldn't use the Number Format option since the KPI is applied to several fields, and some of the fields require decimals formatting, some without decimals, and some with percentage. They have to exist within the same scorecard, thus, we need to apply the formatting based on the field names in the Custom MDX.

    The decimals and non decimals formatting work fine, but the percentage formatting is giving us the issue.
    Tuesday, August 25, 2009 7:06 PM
  • I guess I don't understand this statement "We couldn't use the Number Format option since the KPI is applied to several fields".  Could you please clarify this a bit and maybe provide a small sample of what the scorecard looks like.  I don't understand why you can't use the Number Format column when configuring the Actual and Target values in the KPIs.  The KPI formats are not going to change once they are in the scorecard so I am confused as to what you are doing exactly.  There must be something else going on here.  If you convert or use all decimals in the custom MDX and then use the Number Format option to put this into percentage will this not work as expected?

    I guess I might need you to explain what you are doing in the custom MDX specifically with one of your examples and a sample of your expected output in the scorecard.
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Tuesday, August 25, 2009 7:31 PM