6 Replies Latest reply on Nov 21, 2013 12:40 PM by Jonathan Drummey

# Subtotals calulated on entire data set

I am trying to figure out how I get my subtotals to calculate against the entire data set as opposed to the top N items or dimensions I have selected. In my example, I have top N items by Region but I want to see the actual region totals for all products in my subtotals instead of the summed totals of the items in the chart. In my example, Central subtotal should show 519,862 regardless of the number of items I select. The Grand total should also be against ALL items not the selected ones.

• ###### 1. Re: Subtotals calulated on entire data set

See the attached. You basically just need to use a sorted index with your parameter to only show the Top N while having your subtotal reflect the entirety of your dataset.

I hope this helps,

Dan

• ###### 2. Re: Subtotals calulated on entire data set

Very nice.  And this works because INDEX() is a Table Calculation, which is evaluated after the totals--is that correct?  Whereas the "Top" filter type is evaluated before the totals?

• ###### 3. Re: Subtotals calulated on entire data set

Precisely. A top filter serves as a WHERE clause in the query we send to the database. An index "filter" hides marks from the final visualization rather than filtering them out in the query. We have the results for all of the Products when we use the index() "filter" but we only display the Top N marks.

• ###### 4. Re: Subtotals calulated on entire data set

Nicely done! That's what I was struggling with. Now what If I wanted to take Parameter off and just filter top on the Total Profit by Item, could I still use the Index() function and drop the "<=[Select Top N]" syntax from the formula?

• ###### 5. Re: Subtotals calulated on entire data set

Yes--as long as you are using INDEX() to limit your data, that will always evaluate after the totals are applied to the whole data set.

However, you cannot use INDEX in a Top filter, if that is what you are asking.  You'd just hard-code the value you want to filter at, like:

index()<=5  // display only the top 5 results; Compute Using Settings: Addressing Region, then Item, Restarting Every Region.

Example attached; not sure if this helps or not.

1 of 1 people found this helpful
• ###### 6. Re: Subtotals calulated on entire data set

Here's an alternative description that gets to the same result. I think it's helpful to think of the detail row & subtotal/grand total computations as on separate "tracks." The original measure is separately computed in the detail rows and Subtotal/Grand Total separately, the table calc filter only applies to the original measure, not the Subtotal/Grand Total computation.

This theory is based on looking at the queries that Tableau issues, when possible it will do all the computation in the query for the detail rows, while the subtotal/grand totals are computed inside Tableau.