**Product(s):**Tableau Desktop

**Version(s):**8.2, 8.1

**Last Modified Date:**16 Aug 2016

This article demonstrates how you can use Multidimensional Expressions (MDX) to calculate the weighted average of different fields in a cube data source. Below are two examples of MDX statements that can be used in Tableau to calculate weighted averages.

**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.

## 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.

- Download and open the MDX Weighted Averages Examples workbook and click the
**Weighted Average of Two Measures**worksheet. - Click the Dimensions drop-down menu, and then select
**Calculated Members**. - In the Calculated Members dialog box, click
**New**to create a new calculated member. - In the Calculated Member Definition section, do the following:
- In the
**Name**field, type**Weighted Avg**. - 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]`

`)`

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

- In the

### Step 2: Create the view

- From the Dimensions pane, drag
**Product.Product Categories.Category**to the**Rows**shelf, and then drill-down to**Subcategory**. - 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

- In the MDX Weighted Average Examples workbook, click the
**Arbitrary Complex Weighted Average for Each Product**worksheet. - 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

- From the Dimensions pane, drag
**Product.Product Categories.Category**to the**Columns**shelf, and then drill down to**Subcategory**. - 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.

Alternate Search Terms:MDX, Cubes, Multidimensional data sources, OLAP, String functions, MSAS