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.

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.