Search code examples
ssasmdxmdx-query

MDX - Top N elements of every subgroup

Let's assume I have a cube with a sales fact table and 3 dimensions: Time (with hierarchy year-month-day), Geography (with hierarchy continent-country-region) and Product with some characteristics about the product sold, let's take the brand for instance.

What I am trying to do is to display the top N brands with respect to the measure chosen, in this case average sales (which is already in the cube as total sales/number of products sold), for every region and month.

I also need to display the country and the year, for clearness.

I have searched for the solution to this everywhere, I came close to it but not completely. I hope someone can help me figure it out.

So I used generate and topcount with the following query, but the problem is that the topcount calculates the N best selling brands over the whole dataset, not for every region and month subgroup. And then applies this top N to every subgroup.

WITH SET [Top N Brands] AS
    Generate([Geography].[Region].Children,
    TopCount(Geography].[Region].CurrentMember * [Gpu Product].[Brand].[Brand].MEMBERS,
            5, ([Measures].[averageSales])))
SELECT {[Measures].[averageSales]} ON COLUMNS,
    NON EMPTY ( [Time].[Year].[Year],
                [Time].[Month].[Month],
                [Geography].[Country].[Country],
                [Top N Brands])  ON ROWS
FROM [Cube]

So I am getting this, with the global top 5 brands distributed over the regions, if sold there:

Wrong result

But I should get this with different top 5s for every region:

Result I should get

What am I missing?

Solution

  • You need to use rank. Take a look at the example below. I am using the sample Adventure Works Db, here I am listing For each country, for each product category in that country, the top three subcategories according to internet sales.

    WITH 
MEMBER [Measures].[Internet Sales Amount Rank] AS
RANK( ([Customer].[Country].currentmember,[Product].[Category].currentmember,[Product].[Subcategory].CurrentMember),
ORDER( ([Customer].[Country].currentmember,[Product].[Category].currentmember,[Product].[Subcategory].[Subcategory].Members) , [Measures].[Internet Sales Amount], BDESC)
) 

select 
non empty 
([Measures].[Internet Sales Amount])
on columns
,
non empty
([Customer].[Country].[Country],
[Product].[Category].[Category],
filter([Product].[Subcategory].[Subcategory],[Measures].[Internet Sales Amount Rank]<4))

on rows 
from [Adventure Works]

    Result

    enter image description here