When working with average lines, sometimes you want to click on a point in your data and still be able to compare to the original overall reference line. Tableau makes it easy to keep your reference line fixed by simply unchecking the “show recalculated line” option when you right-click on the line. This way, when you click on a mark or point in your view, the average line will maintain its value for the overall view, rather than recalculate just for the selected point.

But what if you want to filter to a single value in your view? When you do this, even if you have unchecked the “show recalculated line” option, your average line will recalculate to the filtered value. HALP!

In order to work around this, you need to create a fixed level of detail calculation. Usually, level of detail calculations consist of two parts enclosed in curly brackets: 1. The first part tells the calculations what to do with the dimension (Should it be fixed at the dimension? Include the dimension at all times, regardless of what’s in the view? Or exclude the dimension at all times?); 2. The second part is your actual calculation that will define how your dimension is aggregated in the view.

So when you’re trying to fix your average line in your view, regardless of what it’s filtering on, you want to start off by using the FIXED element in the first part of your expression. Like so:

{FIXED: AVG(Sales)}

Now that you’ve got the first part, what should the second part be? Well, here’s the thing, if you’re just fixing your average line to be on the overall average of sales in your view, you’ve already got your aggregate calculated. Level of detail calculations DO NOT actually NEED the second part of the expression! (Interestingly enough, you don’t even need to have the word FIXED in your expression, if you just enclose AVG(Sales) in curly brackets, Tableau automatically defaults to a FIXED level of detail expression). Once you have your fixed level of detail calc, drag it onto detail, and then edit your average line so that it references your calculation. Your end result is an average line that remains fixed and constant to the overall average of what you have in your view, regardless of where you click or filter to.