Adding Reference Line for Weighted Average in Tableau

Question

I've got a bar chart with three months worth of data. Each column in the chart is one month's data showing the percentage of Rows that met a certain criterion for that month. In the first month, 100% of 2 rows meet the measure. In the second month, 24.2% of 641 rows meet the measure. In the 3rd month, 28.3% of 1004 rows meet the measure. My reference line which is supposed to show the average across the entire time-frame is showing 50.8%, the simple average (i.e. [100+24.2+28.3]/3) instead of the weighted average (i.e. [100*2+641*24.2+1004*28.3]/[2+641+1004]).

In the rows shelf, I have a measure called "% that meet the criterion", this is defined as SUM([Criterion])/SUM([NUMBER OF RECORDS])

The criterion measure is 1 for any record that qualifies and null for any that do not qualify.

If I go to Analysis >> Totals >> Show Row Grand Totals, a 4th bar is added, and that bar shows the correct weighted average of the other three bars (26.8%), but I really want this to be shown as a reference line instead of having an extra bar on the chart. (Adding the Grand Total bar also drops the reference line down to 44.8%, which is the simple average of the 4 bars now shown on the chart--I can't think of a less useful piece of information than that).

How can I add the weighted average as a reference line?

Answer 1

Instead of using 'Average' as your aggregation, try using 'Total' instead in the Edit Reference Line dialogue window.

I have to say it's a bit counter-intuitive, but this is what the Tableau online help has to say about it:

http://onlinehelp.tableau.com/current/pro/online/mac/en-us/reflines_addlines.html

Total - places a line at the aggregate of all the values in either the cell, pane, or the entire view. This option is particularly useful when computing a weighted average rather than an average of averages. It is also useful when working with a calculation with a custom aggregation. The total is computed using the underlying data and behaves the same as selecting one of the totals option the Analysis menu.

Answer 2

If you are using Tableau 9, you can make second calculated field using an LOD expression

{ SUM([Criterion]) / SUM([NUMBER OF RECORDS]) }

This will calculate the ratio for the entire data set after applying context and data source filters, without partitioning the data by any of the other dimensions in your view (such as month in your case)

If you place that new field on the detail shelf then you can use it to create a reference line.

There are other ways to generate a weighted average, but this is probably the simplest in your case.