Results 1 to 2 of 2

Thread: How to calculate a weighted average

  1. #1
    Join Date
    Nov 2008
    Posts
    4

    How to calculate a weighted average

    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!!

  2. #2
    Join Date
    Dec 2009
    Posts
    4

    sum descendants

    Have you tried something like this:

    SUM(Descendants([Company].[Geography].CurrentMember,[Company].[Geography].[Company], self),
    [MEASURES].[avg_customer_revenue] * [Measures].[total_customers])

    where [Company].[Geography] is your hierarchy?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •