I2024.2.7 BI Features & Improvements Calc Group Expression Overrides
Business Problem
Calculated Measures that use the Calculation Group References to compute numerator & denominator separately, aka "Non-Aggregated Ratios", will produce unintuitive results when crossed with typical Time Calculation Growth Expressions (e.g. YOY Growth %).
Example:[Sales Amount Per Order] =
|
([Measures].[Sales Amount], [TimeDim].[TimeCalcs].CurrentMember) / ([Measures].[Order Count], [TimeDim].[TimeCalcs].CurrentMember) |
When [TimeDim].[TimeCalcs].CurrentMember is "Year over Year Growth %" and the growth is negative, then:
| [Sales Amount Per Order] = -20% / -20% = 100% |
Correct, but NOT the desired result from crossing a Ratio Measure with a standard Growth Calculation.
Solution
Allow Calculated Measure to override specific Calculation Group Expressions locally.
Example:[Sales Amount Per Order] =
|
CASE [TimeDim].[TimeCalcs].CurrentMember.MEMBER_NAME WHEN "MTD LY Chg" THEN
((([Measures].[Sales Amount], [TimeDim].[TimeCalcs].[MTD]) / ([Measures].[Order Count], [TimeDim].[TimeCalcs].[MTD])) - (([Measures].[Sales Amount], [TimeDim].[TimeCalcs].[MTD LY]) / ([Measures].[Order Count], [TimeDim].[TimeCalcs].[MTD LY])))
ELSE ([Measures].[Sales Amount], [TimeDim].[TimeCalcs].CurrentMember) / ([Measures].[Order Count], [TimeDim].[TimeCalcs].CurrentMember) END |
Summary
- Non-Aggregated Ratios may produce undesired results when crossed with Growth Metrics in a Calculation Group (introduced in i2024.2.4)
- Can now use Calculation Group Member References with CASE and IIF statements to override specific Calculations in a Calculation Group.
- Useful for making Non-Aggregatable Ratios work with Calculation Groups containing Growth Metrics.
- Preserve the simplified user experience for Report End-Users!
- Available: i2024.2.7, c2025.1.0. See Docs: Referencing Calculation Groups in Calculated Measures