I have the same issue - similarly I can understand the average moving if I exclude data, but if I hide it, I feel the ref line should stay the same.
I added red dotted ref line that uses the table calc to get an average or all values in the partition from the underlying data source instead of a normal reference line that is an average of the marks displayed.
a calc field like:
will produce the number you are looking for. You can place it on the Level of Detail shelf, and use it to add a reference line.
rrp_sample_edit.twbx 100.9 KB
I wonder if I can add a complication to this, and try to get appropriate reference lines.
If I were to include week(sett_date) on the left of the columns shelf, thus breaking the month into weeks, could I somehow calculate the average for each week within the month and include it as a reference line.
yes, after adding your extra dimension, simply right-click on the table calc pill on the Level of Detail shelf, and select "Edit Table Calculation" From this dialog, change the compute using to "Advanced" and only bring over the option "Month Day Year of SETT_DATE", and now the ref line will be the average of the underlying vales within each week partition.
This is done in the attached.
rrp_sample_edit_1.twbx 102.2 KB
I have yet to find something Tableau cannot do.
>I have yet to find something Tableau cannot do
We have a few clients on 5.2 and an upgrade not the quickest option at this point. This solution doesn't seem to work on that version. We need the weighted average (in a histogram). How can we achieve this for tableau 5.2?
EDIT - Specifically, what can be used instead of TOTAL() in version 5.2?
Before Tableau 6.0, I would either heavily preprocess my data or use subqueries with custom SQL.
I highly recommend upgrading to the latest version of Tableau.