top of page

Weighted Average by Guest Blogger Cathy Young!

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.



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.



100 views0 comments

Comments


bottom of page