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

• Friday, March 01, 2013 5:05 PM

   

   
  

  0

  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!

  • Reply
  • Quote