Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 85

There isn't a built-in average aggregation type—as we'll soon see,
AverageOfChildren does not do a simple average—but it's very easy to create
a calculated measure that returns an average by dividing a measure with the
AggregateFunction SUM property by one with the AggregateFunction COUNT
property, for example:
CREATE MEMBER CURRENTCUBE.[Measures].[Average Measure Example] AS
IIF([Measures].[Count Measure]=0, NULL,
[Measures].[Sum Measure]/[Measures].[Count Measure]);
DistinctCount
The DistinctCount aggregation type counts the number of distinct values in a
column in your fact table, similar to a Count(Distinct) in SQL. It's generally used in
scenarios where you're counting some kind of key, for example, finding the number
of unique customers who bought a particular product in a given time period. This is,
by its very nature, an expensive operation for Analysis Services and queries that use
DistinctCount measures can perform worse than those which use additive measures;
we'll discuss performance tuning for DistinctCount in Chapter 8 , Query Performance
Tuning . It is possible to get distinct count values using MDX calculations, but this
almost always performs worse; it is also possible to use many-to-many dimensions
(discussed in the next chapter) to get the same results and this may perform better in
some circumstances (see the section on Distinct Count in the Many to Many Revolution
white paper, available at http://tinyurl.com/m2mrev ).
When you create a new distinct count measure, SQL Server Data Tools ( SSDT ) will
create a new measure group to hold it automatically. Each distinct count measure
needs to be put into its own measure group for query performance reasons, and
although it is possible to override SSDT and create a distinct count measure in
an existing measure group with measures that have other aggregation types,
we strongly recommend that you do not do this.
None
The None aggregation type simply means that no aggregation takes place on the
measure at all. Although, it might seem that a measure with this aggregation type
displays no values at all, that's not true; it only contains values at the lowest possible
granularity in the cube, at the intersection of the key attributes of all the dimensions.
It's very rarely used, and only makes sense for values such as prices that should
never be aggregated.