MDX calculated member for average sales for top 10 persons

Question

Supposing I have an electronics store with 10 types of articles and 50 sales persons. What can I do to get the average number of sold articles for the 10 persons with more sales for each type of article?

I started creating named sets for the top 10 persons for each article:

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Persons - Laptops]
 AS TopCount([Dim Person].[Person Name].Members, 10, ([Measures].[Sales], [Dim Article].[Article Name].&[Laptop]));

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Persons - TVs]
 AS TopCount([Dim Person].[Person Name].Members, 10, ([Measures].[Sales], [Dim Article].[Article Name].&[TV]));

I would do this for the other eight.

Then I was trying to create a calculated member for each article type. Each calculated member would sum the total sales for a particular article for the top 10 persons included in the corresponding named set, and then just divide that by 10.

How can I do this? Or, would you recommend a different approach?

Answer 1

I was able to find a solution to this. It is not necessary to create the named set. This is what I ended up doing:

Avg(TopCount([Dim Person].[Person Name].Children, 10, ([Measures].[Sales], [Dim Article].[Article Name].&[Laptop])), ([Measures].[Sales], [Dim Article].[Article Name].&[Laptop]))