KNOWLEDGE BASE

Calculating Weighted Averages Using Multidimensional Expressions (MDX)


Published: 24 Apr 2017
Last Modified Date: 24 Apr 2017

Question

How to use Multidimensional Expressions (MDX) to calculate the weighted average of different fields in a cube data source.

Environment

  • Tableau Desktop
  • Multidimensional Expressions (MDX)

Answer

Below are two examples of MDX statements that can be used in Tableau to calculate weighted averages.

Calculate the Weighted Average of Two Measures

Follow the steps below to calculate the weighted average of Internet Freight Cost and Internet Order Count by using a calculated member.

Step 1: Create a calculated member to calculate the weighted average of two measures.

  1. Download and open the MDX Weighted Averages Examples workbook and click the Weighted Average of Two Measures worksheet.
  2. Click the Dimensions drop-down menu, and then select Calculated Members.
  3. In the Calculated Members dialog box, click New to create a new calculated member.
  4. In the Calculated Member Definition section, do the following:

     

    1. In the Name field, type Weighted Avg.
    2. In the formula box, type the following MDX statement:

      SUM(

      [product categories].currentmember.children, [internet order count] * [internet freight cost])

      / SUM(

      [product categories].currentmember.children, [internet order count]

      )

    3. Confirm that the status message indicates that the formula is valid by clicking Check Formula, and then click OK.

Step 2: Create the view

  1. From the Dimensions pane, drag Product.Product Categories.Category to the Rows shelf, and then drill-down to Subcategory.
  2. From the Measures pane, drag the calculated member Weighted Avg to the Columns shelf.

The weighted averages of Internet Order Count and Internet Freight Cost for each sub-category are now shown.

Calculate the Arbitrary Complex Weighted Average for Each Product

The following example guides you through creating a MDX statement across both the product and customer geography dimensions. This complex weighted average example tells Tableau to get the weighted average of Internet Sales Amount, across each combination of Subcategory and Country, for their respective descendants.

Step 1: Create a calculated member to find the weighted average for each product

  1. In the MDX Weighted Average Examples workbook, click the Arbitrary Complex Weighted Average for Each Product worksheet.
  2. Create a new calculated member, name it Complex Weighted Average, and use the following MDX statement in the formula:

    Sum(

    CrossJoin(

    Descendants ([Product].[Product Categories].CurrentMember,

    [Product].[Product Categories].[Subcategory], SELF),

    Descendants ([Customer].[Customer Geography].CurrentMember,

    [Customer].[Customer Geography].[Country], SELF)

    )

    ,([Measures].[Internet Sales Amount] * [Measures].[Internet Order Quantity])

    ) / [Measures].[Internet Order Quantity]

Step 2: Create the view

  1. From the Dimensions pane, drag Product.Product Categories.Category to the Columns shelf, and then drill down to Subcategory.
  2. From the Measures pane, drag Internet Sales Amount to the Marks card, and then drag Complex Weighted Average to the Rows shelf.

The following view shows the weighted average of Internet Sales Amount, across each combination of Subcategory and Country, for their descendants.

 

Additional Information

Note: The attached workbook contains a connection to the Microsoft Analysis Services (MSAS) AdventureWorks cube. This is a sample cube that ships with Microsoft Analysis Services. To use this workbook, you must change the Server value from scdemo-dbs to the name of your MSAS server that hosts AdventureWorks.
Did this article resolve the issue?