Weighted Averages Calculation

Published: 29 Mar 2017
Last Modified Date: 20 Jul 2023

Question

How to calculate weighted averages in order to compare the results from using a weighted average versus an unweighted average to summarize the data.

Environment

Tableau Desktop

Answer

Averages are used to summarize data results. However, the type of average chosen to represent the results can affect the overall conclusions drawn from the data.

Raw Data

The data in this example includes survey results for two different survey tests summarized by the four groups who took each test, their respective sample sizes, and their frequency of affirmative answers. The corresponding Tableau packaged workbook, Weighted Average Calculation.twbx, is attached to this article for you to download and use along with the steps in this article.

Step 1: Calculating the Weighted Average

The weighted average of the results is calculated by factoring in the sample size with the rate of affirmative answers. This helps to create a more even comparison between the two test results. The general formula for calculating a weighted average is as follows: SUM (Weight * Value) / SUM (Weight)

The following steps show how to create a calculated field in Tableau and adjust this formula for the attached workbook.

Select Analysis > Create Calculated Field.
In the Calculated Field dialog box, complete the following steps.
1. For Name, type Weighted Average.
2. In the Formula box, build the weighted averages formula for the attached workbook’s data source:
  SUM([Frequency]*[Sample Size])/SUM([Sample Size])
Confirm that the status message indicates that the formula is valid, and then click OK.

Step 2: Compare the Results

The last tab in the attached workbook shows a comparison in using weighted vs. unweighted average (average frequency) to summarize the results.

The unweighted average shows that Test 2 has the highest frequency of affirmative responses.
However, the weighted average shows that Test 1 has the highest frequency of affirmative responses.