 Jim Dehner

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

## Weighted Averages

Lately, there have been a lot of Forum questions about calculating weighted averages

Mathematically, a weighted average is defined as the sum of the individual weights x the values divided by the total of the weights #### 1 Table Calculation Example

Posters are familiar with how to find weighted averages with a spreadsheet calculator

But now with how it would be done in Tableau –

Start with a table calculation to get the percent of total weight :

Weight Percent of total = Sum({Weight]) / TOTAL(Sum(Weight]))

then the row-level weighted averages are:

Weighted Average =  [Weight percent of total]*sum([Value])

At the row level, the calculations are the same as the spreadsheet calculation
If we add Grand Total – the totals are not correct?

The solution is to include a formula that forces a value into the Grand Total line and the Wtd Avg in all other rows

Size() = 1 is the Grand Total and Sub Total rows or columns

If SIZE()=1 then
window_sum(sum([Value]*[Weight]))/window_sum(sum([Weight]))
else [Wtd Avg] end

and the result returns the expected values: The solution also works when another dimension is added set the table calculations to Pane Down Table calculations are last in the Order of Operations so when possible I use LOD's – I also think the LOD approach is easier to understand  – see the next 3 examples

## 2 – Toy Store Example

Start with a toy store data set – 3 categories with 4 products in each – and determine the weighted average sales by category and overall The weights are the number of units sold and the values are the dollars –

The numerator can be calculated with an LOD:

{ FIXED [Category],[Item]: sum([Units])*sum([Dollars]) }

The denominator is

({ FIXED  [Category],[Item]:   sum([Units])})

the weighted average is:

sum([LOD Numerator])/sum([LOD Denominator])

by using LODs the viz can be displayed in detail (including the total and subtotals) Or the hierarchy can be compressed to show the summary #### 3 Crime Rate Example

This example uses crime data broken out by region, state, and city to find the crime rate per 1000 population at the state and region level

The data is organized like this The crime rate per 1000 at the city level can be expressed using Include LOD's – the Include statement at the city level will automatically accumulate the hierarchy is collapsed

– Note also the numerator and the denominator are determined separately

Total crime =  sum({ INCLUDE [City]:sum([Nonviolent])+sum([Violent crime])})

Pop In Thousands = sum({ INCLUDE [City]: Round(sum([Population])/1000,1)  })

And State Level Wt Avg =
{ FIXED [State]:sum([Total crime])/sum([Pop In Thousands])}

And at the City level      sum([Total crime])/sum([Pop In Thousands])

Display the results in map form using a viz in the tooltip to display the crime rate at the city level as bars and the state level weighted average as reference lines Create a bar chart at the state/city level for city-level crime rate and include a reference line for the state weighted average crime rate and add a reference line to the columns Finally on the map – place the detail sheet in the tooltip and filter for State Hover over a city and the detail chart filtered at the state level opens in the tooltip #### 4 Weighted Average Interest Rate

A common use case is to determine the weighted average interest across a portfolio of loans Given the face value of the loan, an the interest rate the first step is to find the interest in dollars for each loan

Interest Dollars   =    sum([Load Amount]*[Interest Rate])

the weighted average interest rate using an Include LOD (using include the rate will roll up as the hierarchy is collapsed

Weighted ave interest rate  =

[Interest in dollars]/
sum({ INCLUDE [Loan Purpose], [ID number] : sum([Load Amount]) })

At the detail level, the viz is simply Collapsing the hierarchy to the loan purpose level and adding in the Grand Total returns the line of business and total portfolio weighted averages I hope this helps you understand how to calculate weighted averages – the workbooks containing the examples used here can be found at

Enjoy
Jim