Below are some basic examples of functions that may be useful in your analysis.
Variance/Difference between Two Simple Measures.
[Measures].[MEASURE] – [Measures].[MEASURE2]
Formatting: Currency(R$), Standard(Integer), Percent(%) and With Two Decimal Places(12.34).
Currency, Standard, Percent, "#,##0.00"
Percentage Variation between Two Measures.
[Measures].[MEASURE] / [Measures].[MEASURE2]
Conditional Currency Formatting + Colors. The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange).
Iif(([Measures].[MEASURE] < 0.0), "|R$#,##0.00|style=green", "|R$#,##0.00|style=red")
Percentage Conditional Formatting + Traffic Light Alert.
Iif(([Measures].[MEASURE] > 0.0), "|0.00%|image=/resources/images/kpi/green-ball.gif","|0.00%|image=/resources/images/kpi/ball-red.gif")
Accumulated Measurement Year.
Sum(PeriodsToDate([PERIOD.(Complete)].[Year]), [Measures].[MEASURE])
Historical Measurement 12 Months.
([Measures].[MEASURE], [PERIOD.(Complete)].CurrentMember.Lag(12))
Conditional Formatting Percent + Up/Down Arrow.
Iif(([Measures].[MEASURE] > 0.0), "|0.00%|arrow=up", "|0.00%|arrow=down")
Filter only the valid members of the "TOP 100". Generally used in ABC analysis, may vary the value of the "TOP".
NON EMPTY Filter(TopCount({[DIMENSION].Children}, 100.0, [Measures].[MEASURE]), Not IsEmpty([Measures].[MEASURE])) ON ROWS
Member Current Day.
MEMBER [Measures].[Current Day] AS(Iif((Format(now(), "Mm") = [PERIOD].CurrentMember.Name) AND (Format(now(), "yyyy") = [PERIOD].CurrentMember.Parent.Name),CDbl(Format(Now(), "d")), [PERIOD].CurrentMember.Children.Count)))
Member Previous Day.
MEMBER [Measures].[PreviousDay] AS Iif(([Measures].[CurrentDay] = 1), 1, ([Measures].[CurrentDay] – 1))
Member Days of Month.
MEMBER [Measures].[DaysOfTheMonth] AS Sum(PeriodsToDate([PERIOD].[Month]), 1.0)
Member Last Day of the Month.
MEMBER [Measures].[LastDayMonth] AS (([PERIOD].CurrentMember.LastChild),[Measures].[DaysMonth])
Member Remaining Days.
MEMBER [Measures].[DaysRemainingMonths] AS Iif((([Measures].[LastDayMonth] = [Measures].[CurrentDay]),0, ([Measures].[LastDayMonth] – [Measures].[PreviousDay]))
Average Last 3 Months.
Avg(([PERIOD].CurrentMember.Lag(3.0) : [PERIOD].CurrentMember.Lag(1.0)), [Measures].[MEASURE])
Function to Round Measurement to 2 Decimal Places (may vary "2").
(ROUND([Measures].[MEASURE], 2)
Its function is to prevent an invalid value from being displayed as the result of a calculation.
Iif((([Measures].[MEASURE] = 0 OR IsEmpty([Measures].[MEASURE])), Null, "Calculation")
Its function is to prevent an invalid value from being displayed as the result of a calculation by considering both measures of the calculation (Compound Expression).
Iif((([Measures].[MEASURE] = 0 OR IsEmpty([Measures].[MEASURE])) OR([Measures].[MEASURE2] = 0 OR IsEmpty([Measures].[MEASURE2]))), Null, "Calculation")
Its function is to prevent an invalid value from being displayed as the result of a calculation by conditionalizing the measures separately (Conditional).
Iif((([Measures].[MEASURE] = 0 OR IsEmpty([Measures].[MEASURE])), Iif(([Measures].[MEASURE2] = 0 OR IsEmpty([Measures].[MEASURE2])), Null, 1), "Calculation")
Formatting a Measure, displaying the percentage mask + colors (Three Color Ranges). The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange).
Iif(([Measures].[MEASURE] < 0.90), "|0.00%|style=red",Iif(([Measures].[MEASURE] >= 1), "|0.00%|style=green", "|0.00%|style=yellow"))
Formatting a Measure, displaying the percentage mask + color (Red Alert). The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange color).
Iif(([Measures].[MEASURE] < 0), "|0.00%|style=red", "|0.00%|")
Formatting a Measure, displaying the percentage mask + colors (Two Colors + Exception). The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange).
Iif(([Measures].[MEASURE] < 0), "|0.00%|style=red", Iif([Measures].[MEASURE] > 0.40), "|0.00%|style=green", "|0.00%|")
Formatting a Measure, displaying the percentage mask + colors (Three Colors + Exception). The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange).
Iif((([Measures].[MEASURE] < 0), "|0.00%|style=red", Iif(([Measures].[MEASURE] > (0.4)), "|0.00%|style=green",Iif(([Measures].[MEASURE] < (0.18)), "|0.00%|style=yellow", "|0.00%|")))
Conditional Measure by Dimension/Member.
([DIMENSION].[MEMBER], [Measures].[MEASURE])
Simple Aggregate Member Construction.
MEMBER [DIMENSION].[ALL].[MEMBER NAME] AS Aggregate({[DIMENSION].[MEMBER], [DIMENSION].[MEMBER2]})
Construction of Aggregate Members in Range. Considering the First member after the end of the Range ([DIMENSION].[FINAL MEMBER].Lag(1) ) as the final member.
MEMBER [DIMENSION].[ALL].[MEMBER NAME] AS Aggregate({[DIMENSION].[BEGINNING MEMBER] : [DIMENSION].[END MEMBER].Lag(1)})
Construction of Aggregate Members with Exception of Members in the Aggregation.
MEMBER [DIMENSION].[MEMBER NAME] ASAggregate({Except ([DIMENSION].Children,{[DIMENSION].[EXCEPT MEMBER], [DIMENSION].[EXCEPT MEMBER]})})
Present Members of the Dimension disregarding the Exceptions.
NON EMPTY {Except([DIMENSION].Children,{[DIMENSION].[EXCEPT MEMBER], [DIMENSION].[EXCEPT MEMBER]})} ON ROWS
Conditional Formatting Medals.
Iif(([Measures].[MEASURE] >= 9), "|GOLD|image=images/kpi/medal_gold.png", Iif(([Measures].[MEASURE] >= 8.0), "|SILVER|image=images/kpi/medal-silver.png","|BRONZE|image=images/kpi/medal-bronze.png"))
Formatting a Measure, displaying the percentage mask + Traffic Light Alert (3 Colors). The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange).
Iif((([Measures].[MEASURE] < 0.90), "|0.00%|image=/resources/images/kpi/ball-red.gif", Iif(([Measures].[MEASURE] >= 1), "|0.00%|image=/resources/images/kpi/green-ball.gif", "|0.00%|image=/resources/images/kpi/ball-yellow.gif"))
Formatting a Conditional Measure, displaying the percentage mask + Traffic Light Alert (3 Colors). The default colors are red, yellow and green, if you want others change style=red to customCss=background-color:orange (example for orange).
Iif(([Measures].[MEASURE] < 0), Iif(([Measures].[MEASURE2] < 0), "|0.00%|image=/resources/images/kpi/green-ball.gif", Iif(([Measures]. [MEASURE2] >= 0.1001), "|0.00%|image=/resources/images/kpi/ball-red.gif","|0.00%|image=/resources/images/kpi/ball-yellow. gif")),Iif((([Measures].[MEASURE2] >= 0), "|0.00%|image=/resources/images/kpi/green-ball.gif", Iif((([Measures].[MEASURE2] <= (-0. 1001)), "|0.00%|image=/resources/images/kpi/ball-red.gif","|0.00%|image=/resources/images/kpi/ball-yellow.gif")))
Vertical Participation measure of an analyzed dimension.
Iif((IsEmpty([Measures].[MEASURE]) OR ([Measures].[MEASURE] = 0)), Null, ((([DIMENSION].[All].CurrentMember, [Measures].[MEASURE]) / ([DIMENSION].[NAME_MEMBER], [Measures].[MEASURE])))) Show the Child Members of Dimension.[DIMENSION].[All].Children