13 Replies Latest reply on Oct 30, 2013 1:53 PM by Adam Robins

# incorrect average reference line on "binned" field

I am plotting the amount collected on loans (as a percentage of amount due) vs. the number of loans.  I've created a calculated field to act a a "bin" field, so I can have the y-axis range from 0 to 1 in increments of 0.01 for the collected percentage.  I then move the "bin" field from measure to dimension and made it a continuous field.  The graph displays as expected, however, when I add a reference line for "average", it does not display the correct result.  I'm sure I'm doing something incorrectly, but I can't seem to put my finger on it.  Any assistance would be appreciated.  Sample workbook attached, also an Excel file showing the raw data and "expected" average.  Thanks.

• ###### 1. Re: incorrect average reference line on "binned" field

What is the expected result you are after?  I assume this is not what you are looking for, but an average on the number of loans axis can be added.

• ###### 2. Re: incorrect average reference line on "binned" field

I want the average for the y-axis (percent collected).

• ###### 3. Re: incorrect average reference line on "binned" field

Well, it makes sense that the average of the bins from 0-100 would be 50, right?  I'm not sure what value you expect to display there or how Tableau is supposed to calculate it.  Maybe someone else will have more insight.

If you can tell us the number you expect to show up there, we can help you arrive at the calculation to place on the marks card so it can be used for your reference line.

• ###### 4. Re: incorrect average reference line on "binned" field

You prolly want to show the overall average paid back as the reference line.  Or you could also show the average per customer in your chart.  Either way, you create a calculated field as a measure with something like:

sum([Amount Paid])/sum([Payback Amount]).  Drop that measure on your Marks card and then add a reference line based on it.

That will give you the overall average.  To get the average per customer, I believe you just remove the sum functions, set the measure as a measure average and the reference line as a total.

• ###### 5. Re: incorrect average reference line on "binned" field

I tried that.  It provides the same result.

You are correct.  I’m looking for is the overall weighted average of percent collected over all loans.  It seems simple.

The Excel file I attached to my original post shows that this calculation should come to 43.07%.

• ###### 6. Re: incorrect average reference line on "binned" field

Sorry for the edits, but I went back and used the underlying data to calculate the right averages.  Should be as edited above.  Cheers.

• ###### 7. Re: incorrect average reference line on "binned" field

I believe this is what you want (see attached workbook)--the calculation is explicitly defined as a measure with the calculated field being simply:

AVG([Collected Pct])

Placed on the level of detail, I can now refer to that in the reference line dialog.

• ###### 8. Re: incorrect average reference line on "binned" field

Here's the formulas in excel.  I show the average of your Collected Pct column to be 43.06%.  Thats a little different than the numbers I calculated in the last column.  At any rate it should be the right procedure and then you calculate the average as desired.

• ###### 9. Re: incorrect average reference line on "binned" field

I do not, however, understand why building the AVG into the calculated field “Avg Collected Pct” as AVG()  works differently than placing “Collected Pct” field on the measures bar with an aggregation of “AVG”.

AVG(Collected Pct) vs. AGG(Avg Collected Pct) on the measures bar.

• ###### 10. Re: incorrect average reference line on "binned" field

When you chose to do an AVG of the Collected Pct Bins, you were taking an average of the bin numbers.  What you really wanted was the AVG of Collected PCT, so that had to be defined and placed on the level of detail to refer to it inside the reference line dialog box.

• ###### 11. Re: incorrect average reference line on "binned" field

I understand why averaging the bins does not work.  However, that does not explain why adding reference lines for:

Produce different results:

• ###### 12. Re: incorrect average reference line on "binned" field

Its cause the reference line also has an aggregation...if you set it to total and do the average with the pill or in the calculated field, you get the 43%.  Its a lot like a pivot table, but with more places to make changes.

• ###### 13. Re: incorrect average reference line on "binned" field

Got it.  Thanks