13 Replies Latest reply on Dec 4, 2018 10:56 AM by Yuriy Fal

# Grand Total sums up all data for Lookup Filter

Hi Everyone,

I'm a Tableau Newbie and would really appreciate your help. I have a Lookup filter lookup(min(([Date])),0) that preserves % difference calculation for the view.

However, when I bring in Grand Total for Rows, it ignores the filter and shows data for all the months. I'd like the total of the first row to show up as 256+574+406 = 1,236

Also, when selecting "Apply to Totals" on the lookup filter makes the "Grand Total" blank.

Any tips or techniques that can help resolve this issue?

• ###### 1. Re: Grand Total sums up all data for Lookup Filter

Hi Karthik,

It seems an easy problem to solve using LODs.
Would you only use those 3 columns for years? If that is the case, let me know and I can draft an aswer for you.

Thanks,

• ###### 2. Re: Grand Total sums up all data for Lookup Filter

Thanks Alejandro. The example was just for "Last 3 months". However, I have a relative date filter and would like the Grand Total to sum up depending on selection made.

• ###### 3. Re: Grand Total sums up all data for Lookup Filter

A LOOKUP calc used as a filter is a table calc.

When you use a table calc, it does not delete rows from the underlying table like a quick calc does.  It leaves the whole table intact, and just controls what portion of the table gets displayed.

This is by design.  Often we want to display only one [Segment] or [State] or whatever, and still display some percent of overall sales for that state.  The table calc used to generate that percentage needs access to all the states or segments to calc that percentage.  (Just like you are doing on your sheet.)

So your grand total is dutifully totaling up the underlying table.

Now that we have the magic of LODs, we can grab the overall total regardless of the filtered data using FIXED LOD.

{ FIXED : SUM([whatever measure]) }

This will give you the total across the board before filters are evaluated.

• ###### 4. Re: Grand Total sums up all data for Lookup Filter

Ok, I don't get the issue quite well since a simple sum gets recalculated with the filters (see book attached)

However if it is a calculated field you can always use an LOD.
I think the {EXCLUDE} one will help since it will help you get rid of the time component. Also you can try the {FIXED} to have the calculation stuck to one attribute.

Let me know if that helps.

• ###### 5. Re: Grand Total sums up all data for Lookup Filter

Hello Joe,

I'm not sure I quite understand how to bring in the FIXED LOD calc ({ FIXED : SUM([Responses]) }) to resolve the Grand Total issue.

• ###### 6. Re: Grand Total sums up all data for Lookup Filter

Rather than use a table calc to generate your individual percentages, you would use SUM([individual value])/SUM([FIXED LOD])

And then you can filter with a quick filter, and the grand total will be correct across the selected values.

1 of 1 people found this helpful
• ###### 7. Re: Grand Total sums up all data for Lookup Filter

The individual percentages are nothing but the % Difference to the last period across top categories. Is this something that can be achieved with a different calc?

• ###### 8. Re: Grand Total sums up all data for Lookup Filter

Hello Joe and Alejandro,

Here's the packaged workbook. Please let me know if this helps clarifying the issue. Thanks so much

• ###### 9. Re: Grand Total sums up all data for Lookup Filter

Hi Karthik,

You're trying to solve the two problems at once.

First, to have the correct Total figures after

(while) filtering out some Periods.

Second, to preserve Period-over-Period comparisons,

namely, the % Difference relative to Previous (Period).

LOD expressions (specifically, FIXED LOD as in Joe's example)

could help with the former, but couldn't do much with the latter.

So you'd be using Table Calculations (and Table Calc Filters).

Following this logic, one would likely to explore

a path to Custom Totals (using Table Calculations).

And as soon as Custom Totals are mentioned,

I'd like to refer to the seminal article by Jonathan Drummey:

Customizing Grand Totals – Part 2 | Drawing with Numbers

For the Custom Grand Totals using Table Calculation(s),

the Dimension (copy) approach is applied.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 10. Re: Grand Total sums up all data for Lookup Filter

Hi Yuriy,

This is wonderful and thanks so much.

I think I'm getting the hang of Table Calculation and Table Calc filters. I would need to follow and practice all of Jonathan Drummey's examples as I try and learn some of the important techniques in Tableau.

I mirrored the calculations that you'd created for the actual data set and it is working really well. The grand totals for rows appears to duplicate for every column. Am I missing something?

1 of 1 people found this helpful
• ###### 11. Re: Grand Total sums up all data for Lookup Filter

Hi Karthik,

There's no way to escape doubling the Row GT Marks in this case.

But you can set the Main Menu --> Analysis --> Stack Marks --> Off.

This would do the trick with the table display.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 12. Re: Grand Total sums up all data for Lookup Filter

Thank you Yuriy Fal Appreciate your help in this. This is awesome

• ###### 13. Re: Grand Total sums up all data for Lookup Filter

Karthik, you're welcome.