Summary row with different aggregate function for each column
Today my customer ask me a question about how to write the MDX with a summary row however this row with different aggregate functions for each column.
We know in DeepSee analyzer has pivot table option "Summary", user can select sum, avg ... aggregate functions to get the summary row/column. However we can not specify different aggregate function for each measure column.
Here show the example to use All level and IIF function achieve that. see the example (Holefood cube in Sample namespace) below
WITH MEMBER measures.[with avg summary info] AS
'IIF(Product.CurrentMember.Properties("LEVEL_NUMBER") = 1,
AVG(Product.[Product Category].Members, measures.[amount sold]),
SUM(Product.[Product Category].Members, measures.[amount sold]))'
MEMBER measures.[total count] AS 'SUM(Product.[Product Category].Members)'
SELECT {Product.[Product Category].Members, [Product].[All Product]} ON 1,
{measures.[with avg summary info],measures.[total count]} ON 0
FROM Holefoods
with avg summary i total count
1 Candy 149,187.22 83,833
2 Cereal 513,770.68 83,852
3 Dairy 518,826.37 56,481
4 Fruit 744,168.60 85,819
5 Pasta 1,940,939.62 273,971
6 Seafood 2,655,134.41 75,355
7 Snack 3,111,815.51 267,996
8 Vegetable 884,450.97 110,844
9 All Products 1,314,786.67 1,038,151
============================================
Cheng Cheng