Jim Dehner 

 Tableau Visionary and Ambassador

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

Hey #DataFam

Let’s Talk

Open for

Post Links

 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
 
 
 
 
 
 
 
 
 

Leave a Reply

Your email address will not be published.