13 Replies Latest reply on Sep 12, 2017 2:05 PM by Jennifer VonHagel

# Filtering for Top ten and Grand Totals

Hello,

I am wanting to show a list of top providers based on amount they have been paid. I want the list to be the top 10, and I want to show the total that provider was paid, as well as what % of the total that provider received. The problem is when I filter using the Top tab by 'Paid' field, The grand total line is then reduced to only the total of those top 10 providers, not everyone. The same goes for the % of total (which is a table calculation of total paid by provider).

Ex: Provider ABC total paid = \$50, which is 30% of the total of all providers, but when limited to only show top 10 providers, his % goes up to 50%.

Does anyone know how to make Tableau show the grand total of all providers while only displaying the individual totals and %'s for those who are in the top 10?

Thanks for any insight!

• ###### 1. Re: Filtering for Top ten and Grand Totals

Hi Elizabeth, one option is to hide the rows you don't want to show.

In this example, I have a Set that is Top 10 Customers by Profit. I've put in Subtotals so we can see how things play out. With a Set, we can have the Set field give us an indicator of which Customers are In or Out of the Top 10 Set.

I'm going to right click on Out, then choose Hide.

Now you can see the Subtotal and the Grand Total remain intact.

You don't have to have an In/Out field, you could select multiple Name rows using Shift+Select, right-click, and Hide.

If you want the In/Out field to easily hide the Out's, but don't want to show the In/Out column, we can then hide this whole column:

Right-click on the In-Out Column, un-check Show Header

And we're left with the basic table.

Hope this helps,

Jennifer

1 of 1 people found this helpful
• ###### 2. Re: Filtering for Top ten and Grand Totals

Creating a set is a great idea. That way the top 10 can be dynamic depending on time frame, etc. I can hide the "out" of the set as I don't care to see them and then display the subtotal for the "in" the set. Perfect!

Thanks very much!

• ###### 3. Re: Filtering for Top ten and Grand Totals

Hi again,

I just noticed that my 'In set' isn't actually catching the top 10... its grabbing the top 2-3 or so, and then a handful of others that appear to be random, for a total of 7-8 instead of 10.

To create the set I just right clicked on the "Health System" Dimension, then "Create a set". I then went to the Top tab and clicked "By Field" and left the settings as they were (Top 10 by Paid Sum).

Did I do something wrong in setting up the set? Or is there another reason why the In/Out set is not actually showing the top 10?

Thanks!

• ###### 4. Re: Filtering for Top ten and Grand Totals

Hmm, are you filtering by other dimensions? Can you send a sample of your data?

Best

Jennifer

• ###### 5. Re: Filtering for Top ten and Grand Totals

Yes I am also filtering by time period (current or prior), by county, and by service type (medical, dental, pharmacy, etc.)

Sorry I can't share the data because it has protected medical information. Unless there is a way to share without sharing the underlying data as well....?

• ###### 6. Re: Filtering for Top ten and Grand Totals

Can you mock up how your view is set up using Superstore? They have State and Category that you could use as dimensions...

1 of 1 people found this helpful
• ###### 7. Re: Filtering for Top ten and Grand Totals

Sorry for jumping in.

Put your filters to "context", which might help.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: Filtering for Top ten and Grand Totals

I didn't know about superstore. I'll check that out. Thanks!

• ###### 9. Re: Filtering for Top ten and Grand Totals

FYI

• ###### 10. Re: Filtering for Top ten and Grand Totals

Thanks, that seems to have fixed it! Strange... what does adding it to context mean? Also, will any of my filters that are dynamic (from a parameter) be affected? It all appears to be working still, so I think its good!

• ###### 11. Re: Filtering for Top ten and Grand Totals

In addition to adding the filters to context you could try to filter on the rank: INDEX().

So sort your dimension in the view on Paid, then drag INDEX() to the filter and filter on 1-10.

That would preserve the contents outside of the top 10.

• ###### 12. Re: Filtering for Top ten and Grand Totals

Elizabeth,

Unfortunately, It's difficult to say without seeing data.

One example, but there are a lots. Context Filters in Tableau

I could not find the official helps though.

Thanks,

Shin

• ###### 13. Re: Filtering for Top ten and Grand Totals

Thanks, Shin, your answer was much quicker than what I would have done with sample data .

Elizabeth, here are a couple article I like about context filters:

Best,

Jennifer