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+30)

Solution

Price * ROLLUP(Volume, ..Levels of Price)  / (DROPLEVEL(ROLLUP(Volume, ..Levels of Price) ,"Country","SUM")

Was this helpful?

Yes | Somewhat | No