13 Replies Latest reply on Jun 8, 2018 2:38 PM by Daniel Hodges

# Average reference line excluding grand total

Hi,

I think I'm just missing something simple enough here but how do I have my average reference line exclude the grand total? Here's what I'm getting:

But I would expect my average line to be at 765,734...

Any thoughts?

• ###### 1. Re: Average reference line excluding grand total

Okay, I've figured out a way...

But this is using a LOD calculation: {SUM([Sales])}/{(COUNTD([Category]))}

And then dropping this on the view as a constant line. I'd consider this a bit of a workaround. Now I'm just curious, is there a simpler way of doing this?

1 of 1 people found this helpful
• ###### 2. Re: Average reference line excluding grand total

I have the same problem. The Grand total is skewing the average. How can you drop an average line on to the view that doesn't take into account the Grand Total?

• ###### 3. Re: Average reference line excluding grand total

Hey Angel,

See my above response to myself. Use an LoD calculation such as the one I have used and drop it in as a constant line. If you're having trouble, I'm happy to help if you want to upload a package workbook.

Thanks,

Stephen

• ###### 4. Re: Average reference line excluding grand total

Yes, would love your help, actually. What's the easiest way to upload the packaged workbook?

• ###### 5. Re: Average reference line excluding grand total

When you go to save your workbook you'll be given the option to save as a package workbook:

Do this and then in your forum reply to me go to adavance editor in the top right-hand corner of the window:

At the bottom of the advanced editor screen you will see the option to attach your package workbook:

• ###### 6. Re: Average reference line excluding grand total

HI Stephen. Thanks so much for the help. Attached is the packaged workbook. If you look at the tab called "KPI: Reach" you will see the individual months as well as the grand total, which is skewing the overall average.

• ###### 7. Re: Average reference line excluding grand total

Stephen,

You are on the right track. Nice option with the calculated field!

One of the other ways is to use the checkbox at the bottom when you edit the reference AVG line that recalculates the value when items are manually selected. So even if you have the grand total in the view, you can customize the values to be included in the calculation:

Here the value of Average is close to 20 that exludes grand total.

Thanks,

Dhanashree

• ###### 8. Re: Average reference line excluding grand total

Angel,

Below is the snapshot where average reference line can be calculated for EACH PANE

Hope that helps somewhat!

Thanks,

Dhanashree

• ###### 9. Re: Average reference line excluding grand total

I guess the takeaway is to:

1. display grand total

2. remove total

3. calculate average for each pane to avoid the skewing.

Thanks,

Dhanashree

• ###### 10. Re: Average reference line excluding grand total

Thanks Dhanashree.

I actually tried that, but the average line appears on the view in a ghosted view, then snaps backs to the other number (the wrong number) when I click on the view. See screenshot.

• ###### 11. Re: Average reference line excluding grand total

You sure that the setting is chosen for PER PANE and not TABLE or CELL?

Which version of Tableau are you using?

I did it in 10 and it seems to work okay.

May be submit bug and technical support team can provide patch download fix.

• ###### 12. Re: Average reference line excluding grand total

I got it figured out. I had the “exclude” box checked within the Filters card. Once I unchecked that it worked. Thanks again, really appreciate the help, it was driving me crazy.

• ###### 13. Re: Average reference line excluding grand total

I was just given this requirement as well and found a different solution than those provided (Per Pane for whatever reason is not working for me).

IF SIZE() > 1 THEN TOTAL(SUM([Amount]))/SIZE() ELSE Null END

Added a reference line and selected average.