Calculate Weighted Average along a hierarchy
Price
Region | Country | Price |
|---|---|---|
EMEA | DEU | 100 |
ASIA | A | 50 |
ASIA | B | 80 |
Volume
Region | Country | Volume |
|---|---|---|
EMEA | DEU | 10 |
ASIA | A | 10 |
ASIA | B | 20 |
Desired Outcome: Average price per region
Region | Avg price |
|---|---|
EMEA | 100 * 10 / 10 |
ASIA | (10*50 + 20*80) / (10+20) |
Solution
Price * ROLLUP(Volume, ..Levels of Price) / (DROPLEVEL(ROLLUP(Volume, ..Levels of Price) ,"Country","SUM")