I think you can utilize the WINDOW_AVG function to do this.
1) Create a calculated field as an IF statement (let's call it "Bar Color":
IF SUM([PerCapitaCost] >= WINDOW_AVG(SUM([PerCapitaCost]))
2) Add the "Bar Color" field to the colors shelf for PerCapitaCost.
3) It may require you to specify that you want the WINDOW_AVG table calculation to calculate "across" your table, or "Compute Using" your date field...
Does that help?
Thanks, Mark! This is perfect. Just to be sure I understand correctly, the reason why we would want to use a WINDOW_AVG as opposed to a regular AVG is because AVG is only line by line while WINDOW_AVG is an aggregation average?
Hmmm, not all the time, technically.
AVG is still an aggregate function. In Tableau, it will return the arithmetic mean for a measure over whatever the finest level of grouping is on the view. If the view groups to the finest level of detail that exists in your data, (i.e., shows all dimensions), then as you said, the result of AVG will be based on a single row.
The family of WINDOW_ aggregation functions work a little differently from the normal aggregation functions..
Think of viewing your data in a regular table grid with rows and columns. Each dimension you add in a Tableau view "splits apart" the results into more groups. If every dimension is added, each group or row on the view represents a single row from the underlying data.
WINDOW_ allows you to create a set of groups(rows) offset from the CURRENT GROUP (ROW) in the grid, even if you don't have all dimensions in the view (so your view isn't as "exploded" as the full data set, whereas the normal aggregate functions always operate for the FINEST GROUP (ROW).
A moving average is in essence a WINDOW calculation.
I can't really think of a better way to word it. It's much less complicated if you look through an example.
Try adding the WINDOW avg and the normal AVG on a table view and play with adding dimensions to the table. Hopefully that will illustrate what I'm saying better.
Makes sense - thanks, Mark. I really appreciate your help!