Calculating Average of TOP(n) to be used as reference value for other calculations

# Calculating Average of TOP(n) to be used as reference value for other calculations

• Friday, March 01, 2013 5:05 PM

Hello all,

I am fairly new to MDX and my current project really gives me a headache. I hope you can help me with that.

We have several stores which are divided into different sales regions. So I built a hierarchy [Sales Region] -> [Store].

Our business wants to see the sales amount of each store compared to the average of the top 3 stores of the same sales region and calculate the difference between theses values. So, basically I have the following starting position:

Sales Region Store Sales amount
-------------------------------------------------
Reg1 Store A 10
Reg1 Store B 20
Reg1 Store C 30
Reg1 Store D 40
Reg2 Store E 20
Reg2 Store F 30
Reg2 Store G 40
Reg2 Store H 50

and so on...

First I need to calculate the benchmark which is the average sales amount of the top 3 stores for each region:
Sales Region Benchmark
-------------------------------------
Reg1 30 (20 + 30 + 40)/3
Reg2 40 (30 + 40 + 50)/3

In the next step, I should be able to use the benchmark value in each row and calculate the difference:
Sales Region Store Sales amount Benchmark Difference
-----------------------------------------------------------------------------------
Reg1 Store A 10 30 -20
Reg1 Store B 20 30 -10
Reg1 Store C 30 30 0
Reg1 Store D 40 30 +10
Reg2 Store E 20 40 -20
Reg2 Store F 30 40 -10
Reg2 Store G 40 40 0
Reg2 Store H 50 40 +10

I am already struggling calculating the benchmark value. Can you at least give me a hint where to start?
I really want to understand how to do this, but it seems like I'm more a relational thinking person ;)

Best regards and thanks!

### All Replies

• Saturday, March 02, 2013 10:14 PM

This can be run against the AdventureWorksDW 2012 tabular database

WITH

//Get Top 3 sales at current level in hierarchy

MEMBER [Measures].[AvgSales] AS
Avg
TopCount ( [Geography].[Geography].CurrentMember.Siblings, 3, [Measures].[Internet Total Sales] ),
[Measures].[Internet Total Sales] )
MEMBER [Measures].[SalesDiff] AS

[Measures].[AvgSales] - [Measures].[Internet Total Sales]
SELECT
{ [Measures].[AvgSales], [Measures].[Internet Total Sales], [Measures].[SalesDiff] } ON 0,
[Geography].[Geography].[Country Region].&[United States].
Children ON 1
FROM [Internet Operation]