SSAS KPI average goal expression

Question

I need to create a KPI where the Goal Expression is the average of the value expression for the entire cube.

Therefore I can use the KPI to tell me if the Value is above or below the average and by how much.

At the moment all I seem to be able to see are hard-coded numbers or lookups for the goal and not one that is created at the time for all values.

would it be something like:

AVG([Measures].[Sales])

All I seem to get is the average for the level that I am currently at...

whytheq whytheq · Accepted Answer · 2017-07-11T22:34:32

Here is the documentation for AVG: https://docs.microsoft.com/en-us/sql/mdx/avg-mdx

So the syntax of the function is: Avg( Set_Expression [ , Numeric_Expression ] )

[Measures].[Sales] is a numeric_expression but you need to specify what set you would like to average over. I suppose if you wanted to get the average across a complete hierarchy then you could use the MEMBERS function to get hold of the complete set e.g.

AVG(
  [Geography].[State-Province].[State-Province].MEMBERS
 ,[Measures].[Sales]
)

SSAS KPI average goal expression

1 Answers