KPI value expressions

I'm trying to understand what forms the value expression of a KPI can take. All the sample I've seen so far have been very simple, like a simple measure expression, e.g.
[Measures].[Internet Sales Amount]

I've also had success specifying a simple tuple, e.g.
([Measures].[Internet Sales Amount], [Product].[Product Model Lines].[Model Name].&[Bike Wash])

But what if I want something more a little more sophisticated For example, what if I want to include two model names in the product slice - say, [Bike Wash] and [Cable Lock]. Or include a range of dates. Or a sliding date window (say, the previous month). Any examples of more sophisticated KPI value expressions available

Answer this question

KPI value expressions

Raghukas
I get the error:
"Aggregate functions cannot be used on calculated members in the Measures dimension"

There's a couple of issues to consider here:
- As Mosha's above blog entry explains, a "hidden" calculated measure is created for KPI properties which are defined with MDX expressions..
- ..which leads to the error above - but you would see the same error if you manually tried to create a calculated measure with this MDX expression
However, if the intent of your expression is to calculate an average over multiple members of a hierarchy, such that the numerator and denominator are separately aggregated first, then you could create a calculated Aggregate() hierarchy member. The KPI expression would reference that member in a tuple, as in this AW query:

>>

With

Member [Product].[Product Model Lines].[KPIProd] as

Aggregate({[Product].[Product Model Lines].[Model Name].&[Bike Wash],

[Product].[Product Model Lines].[Model Name].&[Classic Vest]})

Member [Measures].[KPIVal] as

([Product].[Product Model Lines].[KPIProd],

[Measures].[Internet Average Sales Amount])

select {[Measures].[Internet Sales Amount],

[Measures].[Internet Order Count],

[Measures].[Internet Average Sales Amount],

[Measures].[KPIVal]} on 0,

[Product].[Product Model Lines].[Model Name].Members on 1

from [Adventure Works]

-----------------------------------------------------------------------------------------------------------

Internet Sales Amount Internet Order Count Internet Average Sales Amount KPIVal
Bike Wash $7,218.60 908 $7.95 $29.19
Cable Lock (null) (null) (null) $29.19
Classic Vest $35,687.00 562 $63.50 $29.19
...

>>
Zhen
I never did get a response to this. Anyone out there

Thanks,
Kevin
CodeJingle

Well, maybe Mosha can best clarify this. But I think that you're alluding to his discussion of MDX functions in AS 2005, where his example for Aggregate() implies that it is not in a calculated measure, since the current measure is something else: Ratio. This is consistent with the query above - Aggregate() is used without explicitly specifying the measure, but the subsequent context establishes [Internet Average Sales Amount] as the current measure. Since this, in turn, is defined as: [Internet Sales Amount]/[Internet Order Count], Aggregate() applies individually to these base measures. Then their ratio is computed, as can be seen from the MDX query results.

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

>>

...

New AS2005 functions

Aggregate( [, ] )

Well, this isn't new function, but there were significant changes around it. First, Aggregate works with Distinct Count measure, as well as with semiadditive measures - even when the set for Aggregate includes Time dimension. Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it. I.e. If the current measure is Ratio: Sales/Cost, and we are computing Aggregate({USA, Canada}), then instead of error, it will return Aggregate({USA, Canada},Sales)/Aggregate({USA, Canada},Cost).

>>
Amby
The Aggregate function is generally useful when you want a calculation to work with several measures so that it continues to apply the correct aggregation appropriate to each measure. So for example if you define a YearToDate calc member in a time dimension, it would use sum when applied to [Unit Sales], use LastNonEmpty when applied to [Inventory Level], and use Max when applied to [Peak Call Load]. If you are create a calculation based on a specific measure you can just use the aggregate function appropriate to this measure.
Leng UNG

You can use any MDX expression that can be used in calculated measure to define Value or any other property of KPI. Behind the scenes, there is a calculated measure created for every property of KPI. You can read a little bit more details here: http://www.mosha.com/msolap/articles/cubeinit.htm

For your examples, the expressions will look like following:

Aggregate({[Product].[Product Model Lines].[Model Name].&[Bike Wash], [Product].[Product Model Lines].[Model Name].&[Cable Lock]}, [Measures].[Internet Sales Amount])

HTH,

Mosha

==============================================

Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/weblog/mosha
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================================
Karthik Subramanyam
Sorry - I didn't quite understand what you mean by "it doesn't work for calculated measures". Can you please explain what doesn't work with example if possible.
Joe D. Sullivan III
Well, if I take your example and change it to:

Aggregate({[Product].[Product Model Lines].[Model Name].&[Bike Wash], [Product].[Product Model Lines].[Model Name].&[Cable Lock]}, [Measures].[Internet Average Sales Amount])

I get the error:
"Aggregate functions cannot be used on calculated members in the Measures dimension"
Simon Scott
Eek - having to define a separate, special member just to hold the results of the aggregation would be a bit of a drag. But it sounds like that may be the only option.

One thing that confuses me is that an entry on Mosha's blog says:
"Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it. "

I'm not clear why that doesn't apply here (although the magic word "can" indicates it may not always work). The examples I'm working with don't seem particularly complex, so I'm not sure why it doesn't work in this case.
bankim
Mosha,
Thanks, that works great for base measures. But it doesn't work for calculated measures. Is there a way to do this with calculated measures as well
KPI value expressions

Answer this question