For each measure I can select Aggregate Function to be used when
aggregating with this measure. I can choose among Sum, Count, Min, Max
and Distinct Count.

Is it possible to define more than one aggregate function per measure
?
I need an average but there is no AVG function listed. How to count
average ?

In other words for each member in the dimension I would like to store
3
values: Sum, Avg and Count of its children. Is it possible ?

Thanks for any help.

Radek

Why not to define multiple measures?

James

"Radek" wrote:

Yes, it is possible, but not typically done. It cannot be done for a
physical measure, but you can do it for a calculated measure.
You use this approach when a measure has one formula in one set of
circumstances; and a different formula in another set of circumstances.
For example, you might calculate "profile" one way in division X and another
in division Y. Look in the MDX documentation at the "iif" function.

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Radek" <suspi (AT) lycos (DOT) com> wrote

Well, I think calculated measure is good idea but how to set a custom
rollup formula for calculated measure ? Maybe I did not explain exactly
what I need. I will start with a basic question.

I have two regular measures MeasureA and MeasureB. By default the
aggregation function is set to Sum. It is fine for MeasureA but for
MeasureB I need the aggregation function do be Average. How to set up
this ?

Thanks for help.

Radek


Dave Wickert [MSFT] wrote:

Look at the Cube Editor. Click on the measure. At the bottom left hand
corner is the property box. There is an advanced property on a measure which
is the aggregation function. If you want the arithmetic mean for a measure,
then create two physical measures -- one using SUM; one using COUNT as their
aggreation function. Create a calculated measure which is the SUM divided by
the COUNT measures. Then set the physical measures' visibility false
(another advanced property).
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

<suspi (AT) lycos (DOT) com> wrote

Thanks for hint. Just to make it clear. What aggregation does OLAP use for the
calculated measure ? I suppose it makes all aggregations on regular measures and
than uses these aggregated values for the formula used in the calculated measure.
Right ?

Radek

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote in
news:##gipjh3EHA.1408 (AT) TK2MSFTNGP10 (DOT) phx.gbl: