4 Replies Latest reply on Jun 21, 2012 5:58 PM by Joe Mako

# How do I group and calculate everything outside of the top 10?

Hey Tableau Forum,

Quick question.  Does anyone know how to calculate a Top 10 total (below), and then subtract it from the Grand Total(Below)?  All the data I'm working with is proprietary and I'm unable to share it but I have pasted a simple picture of what I'm trying to do, in hopes that someone can explain fairly easily.

A couple of things to note:

1. The list of clients is far to big to create groups
2. The list of clients is dynamic and will change from month to month so I can't do it using a filter. However, I can use the index function to get the top 10
3. I'm envisioning a formula like: Total(sum(Sales)) - window_sum(sum(Sales)), that presents itself in a single row before the Grand Total
4. The Grand Total Figure should include the top 10 in addition to all other figures not shown
 Rank Client Sales % of Tot. 1 A 5 5% 2 B 5 5% 3 C 5 5% 4 D 5 5% 5 E 5 5% 6 F 5 5% 7 G 5 5% 8 H 5 5% 9 I 5 5% 10 J 5 5% Top 10 Total 50 50% Difference 50 50% Grand Total 100 100%

Any help would be greatly appreciated.

• ###### 1. Re: How do I group and calculate everything outside of the top 10?

Does a single client have multiple rows in the datasource, or just one row per client? Could you mock up some data with with expected result from that data?

• ###### 2. Re: How do I group and calculate everything outside of the top 10?

There are a few ways to accomplish this.

Attached is a workbook with two examples. The first shows a simple Top N with Difference, this method does not provide the results you are looking for, but may help you understand what is going on in the second sheet. The second uses data duplication in the custom SQL to create the nice view you are looking for.

• ###### 3. Re: How do I group and calculate everything outside of the top 10?

Thanks Joe, were getting closer.  The only problem is that I keep getting an error when writting this formula:

CASE ATTR([Type])

WHEN "Data" THEN SUM([AUM (USD)])

WHEN "Top N Total" THEN

IF FIRST()==0 THEN

WINDOW_SUM(SUM([AUM (USD)]),0,IIF(FIRST()==0,[Top 25]-1,0))

END

WHEN "Difference" THEN

IF FIRST()==0 THEN

WINDOW_SUM(SUM([AUM (USD)]),IIF(FIRST()==0,[Top 25],0),IIF(FIRST()==0,LAST(),0))

END

WHEN "Grand Total" THEN

IF FIRST()==0 THEN

TOTAL(SUM([AUM (USD)]))

END

END

The error says that: Cannot Use 'NULL Type in 'IF' expression, do you know what this means?

Thanks

• ###### 4. Re: How do I group and calculate everything outside of the top 10?

In my experience that is one of the odd error messages that has phantom issues, like it has a memory of a time when it did have a possible null value, but no longer does.

Have you tried creating a new calc field and just pasting this full formula into it?

If you still get the error message, then you may need to wrap every instance of SUM([AUM (USD)]) in a ZN() function, so each

SUM([AUM (USD)])

becomes

ZN(SUM([AUM (USD)]))

If that still does not help, I would need to see a packaged workbook that experiences the issue.