MDX - How to present members of hierarchy as multiple columns

Question

I am new to MDX on Analysis Services. So I have a bit of a problem. How do I get max value of child values in a parent?

For example, I am using AdventureWorksDW on SSAS. The model has a hierarchy in Product > Prodct Category > Product Subcategory.

How do I get the maximum value of [Measures].[Internet Sales Amount] for each member of [Product].[Subcategory]?

For example, in the picture above, from this data I want to output :

Accessories $245,529.32

Bikes $14,520,584.04

Clothing $172,950.68

FrankPl FrankPl · Accepted Answer · 2021-07-14T19:47:42

You can use something like this statement:

SELECT [Measures].[Internet Sales Amount]
    ON COLUMNS,
    Generate([Product].[Category].[Category].Members,
             TopCount({[Product].[Category].CurrentMember} * [Product].[Subcategory].[Subcategory].Members,
                      1,  
                      [Measures].[Internet Sales Amount]
                     )
            )
    ON ROWS
FROM [Adventure Works]

The Generate function loops over its first argument, for each loop run calculating a set from the expression in the second argument, and then concatenating all these sets. And this expression takes the top 1 subcategory by Internet Sales Amount per the category from the loop run, with other words, the one which has the maximum value.

To answer the additional question in your comment:

If, instead of showing the child element with the maximum value, you want to see the average value of the children, then this would be a completely different query. Instead of using an existing measure and dealing with the row member selection, you would now define your own measure:

WITH MEMBER Measures.[Avg Child Sales Amount] AS
     Avg([Product].[Product Categories].CurrentMember.Children, [Measures].[Internet Sales Amount])
SELECT { [Measures].[Internet Sales Amount], Measures.[Avg Child Sales Amount]}
    ON COLUMNS,
    [Product].[Category].[Category].Members
    ON ROWS
FROM [Adventure Works]

Please note that - as we need the children here, we need to use the user hierarchy [Product].[Product Categories] in the measure definition, not the attribute hierarchies [Product].[Category] and [Product].[Subcategory], which I used in the first query to be able to show two separate columns on the row headers. The CurrentMember between both, however is linked: The CurrentMember of the user hierarchy is the CurrentMember of the lowest of its constituting attribute hierarchies.

By the way: if you would not be interested in the member which has the maximum value, you could as well define a max measure similar to the avg member I defined in the second query. However, normally the member which has the maximum value is interesting to users, hence this is a technically valid solution, but not a useful one for most use cases.

And, of course, if you need this average measure often, you could also define an additional measure with aggregation method "Average of children" in the cube already. Even users without MDX knowledge could then use it. But that would then average on all levels: The subcategory values would be the average of the constituting products, and the category values the average of the subcategories, while the measure defined above is the sum up to subcategory level and the avg from subcategory to category level.

MDX - How to present members of hierarchy as multiple columns

1 Answers