I've been asked how do you do a weighted average in Tableau? The below article was created by Cathy Young and is a downloadable PDF in her site tableauatwork.com !
Weighted Average with Fixed LOD
This example also uses a FIXED LOD expression, and has two fields (“Category”
and “Order Date”) in the dimension declaration. The LOD’s aggregate expression is simply the calculated field “Avg Expense.”
avg( {FIXED [Category], Year([Order Date]) : ([Avg Expense]) } )
Introduction
This text table shows expenses as a percentage of sales for each year. My audience
wants the average calculation (the column on the right) as a weighted average. Think of this calculation as an average of averages.
Avg ( 96.37 + 98.10 + 96.41 + 98.47 ) = 97.34
Project Files
If you want to follow along with this example, download the Tableau Public file.
Tableau Public file: https://public.tableau.com/app/profile/cathy.y7960/viz/WeightedAverages1047/Dashboard1
Preview - the Finished Dashboard
To illustrate the difference between averages and weighted averages, I created
several views with the data as well as the calculations.
Worksheets & the Dashboard
In addition to the dashboard, there are five worksheets in this workbook.
Profit
Sales
Expense
Avg Expense
Weighted Average
Data & Fields
This chart is based on the Sample Superstore data source and utilizes these fields.
Order Date
On the Columns shelf the data type (Calendar) for “Order Date” is “Date.” For the “Order Date” field, I’m using a “Date Part” with the Date Level “Year.”
Year(Order Date)
Category
The “Category” field is a discrete dimension with a (abc) “String” data type. Because “Category” is a dimension, headings are added to the view. In this case, row headings are added because “Category” is on the Rows shelf. As expected, the field has a blue background or “pill,” because it has a discrete setting.
Sales
The data type # for “Sales” is a “Number (decimal).” In this workbook, “Sales” is
a continuous field in my calculated fields.
Profit
“Profit” is a # number field and is also used in calculations.
Expense
“Expense” is a new calculated field with a data type =# of “Number.” I am not
using this field in the average calculation itself, but I did want to show how I calculated expense using the “Sales” and “Profit” fields.
In the Data pane, the two symbols to the left of the field name are =# green
indicating this new field is continuous. The equal symbol indicates this is a calculated field, while the pound symbol indicates this is a number field.
Weighted Average
“Weighted Average” is a new calculated field with a data type =# of “Number.” The
“Weighted Average” calculated field uses the “avg” aggregation in a “FIXED” Level of
Detail expression. We’ll explore this expression a bit later in this example.
avg( {FIXED [Category], Year([Order Date]) : ([Avg Expense]) } )
Avg Expense
“Avg Expense” is a new calculated field with a data type =# of “Number.” The new
field uses the “Sum” aggregation for the “Sales” and “Profit” fields, wrapped in the
Round() function to change the data into a percentage.
Components or Elements
There are four elements to these views.
Rows Shelf
Columns Shelf
Text Tile on the Marks Card
Row Grand Totals
Columns Shelf
On the Columns shelf the data type (Calendar) for “Order Date” is “Date.” For the “Order Date” field, I choose the Date Level “Year.” Because I checked discrete the field is blue on the Rows shelf.
Rows Shelf
The “Category” field is a discrete dimension with a “String” (Abc) data type. Because “Category” is a dimension headings are added to the view, in this case row headings because “Category” is on the Rows shelf. The heading values are:
Furniture
Office Supplies
Technology
Text Tile on the Marks Card
The “Weighted Average” field on the Text tile on the Marks card reflects four years
of data.
Row Grand Totals
From the Analysis menu, select “Totals” and “Show Rows Grand Totals” to add a
grand total column on the right side of the chart.
Marks Card: Text
To create three views to show Profit, Sales, and Expenses, I use a different field
on the Text tile of the Marks card for each view. Here you can see the continuous field “Expense” is on Text.
To create the other views I duplicate the worksheet and replace the field on the
Marks card.
After connecting to my data source, I create three views - Profit, Sales, and
Expense. This is an optional step; I wanted to have the data available for you while I create the average views.
Next, I add the three views to the dashboard, as shown below.
The Avg Expense View
For this view, I want to replace “SUM(Expense)” with “Avg Expense.” I duplicate the
“Expense” worksheet and drag the new “Avg Expense” field on top of “Expense” on the Text tile on the Marks card. The default “average” aggregation in Tableau computes the average of all values across the row from left to right, using Tableau’s default calculation
for average.
( SUM( Sales ) / SUM( Profit ) ) * 100
Tableau adds a new column to the right of the “Category” values. In this example,
I renamed the column heading “Average all Years.” For this average calculation, first
Tableau calculates the SUM(Sales) total of 754,748, as well as the SUM(Profit) total of 735,018. Then the average for “Furniture” across all years is calculated.
(735,018 / 754,748) = 97.39%
Unfortunately this is not what I was expecting. I don’t want 97.39, I want 97.34.
The Weighted Average Calculation
My audience doesn’t like this default average calculation, and instead wants to see
a “Weighted Average” calculation like this.
(96.37 + 98.10 + 96.41 + 98.47) / 4 = 97.34
Now I’m going to use the “Avg Expense” field in a new “Weighted Average”
calculation with a FIXED scope and two dimensions - “Category” and “Order Date.”
This LOD expression uses a FIXED scope.
The dimension declaration uses the “Category” and “YEAR([Order Date])” fields.
The aggregate expression is [Avg Expense].
The Weighted Average View
For this view, I duplicate the “Average” worksheet and drag the new “Weighted
Average” field on top of “AGG(Avg Expense)” on the Text tile on the Marks card. Tableau adds a new column to the right of the “Category” values. Now the calculation is 97.34, which is the correct calculation: average of averages.
