8 Replies Latest reply on May 8, 2018 7:56 AM by Gerardo Varela

# Is it possible to exclude specific values from a grand total but still show those values in the detail?

Hi,

I'm trying to create a table that shows scores over a rolling 13 month timeframe, but the grand total column aggregates only the YTD data (excludes any records where the date dimension doesn't occur in current year).  Up to this point, I've had to fudge the visualization by creating two separate worksheets (one showing the monthly scores for rolling 13 month timeframe and another showing the YTD total) then put them together in the dashboard to make it look like one table.  This is obviously not ideal for several reasons.  I attached a workbook with a simplified example of what I'm currently doing to get the visualization/calculations that I want.  Is there a way to do this without having to use two separate worksheets?  Thank you!

-Dennis

• ###### 1. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

Hi

Not certain what you are trying to accomplish but this will give you a total you can vary the last 2 values to determine how far back you look

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

Hi Dennis,

As far as I know, you cannot create a custom Grand Total calculation.  We can easily create a calculation that will return each year's monthly average, or even only the current year's YTD average.

YTD Monthly Average

//This gives the monthly average because [Date] is already at a monthly interval

{ FIXED YEAR([Date]) : AVG([Score]) }

This YTD Monthly Average

MAX(

IF YEAR([Date]) = YEAR(TODAY())

THEN

//This gives the monthly average because [Date] is already at a monthly interval

{ FIXED YEAR([Date]) : AVG([Score]) }

END)

The difficulty with your data source as it is, is creating an extra column at the end of the table in which to put the YTD number.

This may be way more trouble than it's worth, but you could trick this out by adding an empty record to your excel sheet, so that in Tableau we can put in the YTD number.

This shows the results of several calcs:

1. I created a Date Text column to show Month if date type is Month or the text YTD if Date Type is YTD.

2. The Avg Score is as it was before

3. I found the Current Years YTD Average

4. The end result shows monthly score for Date Type = Month and the Current YTD Average for Date Type = YTD.

Final result:

The calculations:

Date Text:

IF [Date Type] = 'Month'

THEN left(DATENAME('month',[Date]),3) + ' ' + STR(YEAR([Date]))

ELSE 'YTD'

END

Current YTD Avg:

WINDOW_MAX(MAX(

//Monthly average for current year

IF YEAR([Date]) = YEAR(TODAY())

THEN

//Monthly Average for each year

{ FIXED YEAR([Date]) : AVG([Score]) }

END

))

Avg Score (shows monthly for months and YTD for YTD):

IF ATTR([Date Type]) = 'Month'

THEN AVG([Score])

ELSE [Current YTD Avg]

END

Pasting two sheets together in a dashboard might be easier .

Best,

Jennifer

1 of 1 people found this helpful
• ###### 3. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

Another possible solution in the attached. This might not work on your real data set since this is a simplified version but let me know if you have any questions. The main concept is identifying when you're in the total.  I've used FIRST()=LAST() you can also use MIN()=MAX() or Size().  Go read this awesome three part series:

http://drawingwithnumbers.artisart.org/?s=Custom+Grand+Totals

Regards,

Gerardo

2 of 2 people found this helpful
• ###### 4. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

Whaaaaaaaaaaaaaa??!! This is amazing! All props and correct answer to Gerardo .

Love this forum, learn something new all the time!

Jennifer

• ###### 5. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

Thanks everyone for your suggestions.  Gerardo - looks like you're on the right track.  I'm unable to open the workbook you attached, I have Tableau 10.2.0, I assume you are on a more recent version.  Would you be able to convert and re-post so I can look at your calculations?  I 'll check out the article you posted in the meantime, maybe that can get me there.

• ###### 6. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

Here it is.  I would recommend if you're not on the latest version of Tableau Desktop to include what version you are using when posting a question.  The latest version of Tableau Desktop, 2018.1, doesn't warn you when opening older workbooks.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 7. Re: Is it possible to exclude specific values from a total but still show those values in the detail?

This is great- thanks so much, Gerardo.  Going forward I'll be sure to include Tableau Desktop version in my posts.

• ###### 8. Re: Is it possible to exclude specific values from a grand total but still show those values in the detail?

Hi Dennis,

You're welcome and thanks in advance.

Jennifer VonHagel Glad you found that useful.

Regards,

Gerardo