We want to calculate a weighting value in our hierarchy based on what values do/don't exist at the lower level.

Background:

* Our heirarchy is Region --> Country --> Company
* Our fact table stores two measures at the company level - total_customers and avg_customer_revenue.
* For some companies, we may not have a measure value for avg_customer_revenue
* At the country level we want to calculate an aggregated weighting for avg_customer_revenue that accounts for those companies with a missing avg_customer_revenue value.


Example for France:

CompanyA avg_customer_revenue = no recorded value ($0.00)
CompanyB avg_customer_revenue = $37.17
CompanyC avg_customer_revenue = $32.25

CompanyA total_customers = 3,234,320
CompanyB total_customers = 2,944,000
CompanyC total_customers = 2,694,000


Our formula is:


(CompanyA avg_customer_revenue * CompanyA total_customers) + (CompanyB avg_customer_revenue * CompanyB total_customers)
+ (CompanyC avg_customer_revenue * CompanyC total_customers)
/ (CompanyB total_customers + CompanyC total_customers)


In the denominator we only aggregate total_customers for companies that have an avg_customer_revenue.


The Calculation is:
(0 * 3,234,320) + (37.17 * 2,944,000) + (32.25 * 2,694,000) / (2,944,000 + 2,694,000) = 34.82

So weighted avg_customer_revenue for France is $34.82


We know how to calculate the denominator in MDX, but how can we calculate the numerator? We have experimented with the Aggregate and Generate functions as well as using named sets but nothing has worked so far....

Thanks for your help!!