MDX-How do you format or round KPI expression value?
Q: How do you format KPI expression value?
A: There are few ways you can format KPI expression value:
- Define calculated measures and use them in Value or Goal expressions. Format measures value using FORMAT_STRING. Note that creating separate calculated measure for each KPI expression (Value, Goal, Status and Trend) is highly recommended. When Analysis Services finds in KPI definition expression that is more than reference to another measure, then SSAS creates hidden calculated measure. If you create calculated measure, you have more control on formating and how it is calculated - for example you can use SCOPE to improve calculated measures performance.
- In KPI expression definition you can use format function to change how value is displayed. Example:
Format( [Measures].[a] / [measures].[b], "#,##0.0000")
- Use ROUND function:
ROUND( [Measures].[a] / [measures].[b], 2)