2 Replies Latest reply on Jun 17, 2012 10:30 AM by Joe Mako

# Subtotal needs to sum what is displayed, not what underlies

I have two columns in my Data…Sales and Profit.  My report has cumulative (over time) results, and needs to override the Profit number with a 0 if Sales is 0.  Using the following as sample data:

Sales   Profit

0           10

1           15

2           30

My report needs to show

Sales   Profit

0           0

1           15

2           30

Subtotal: 45

The following works at the detail level:

IF SUM([Sales]) = 0 THEN 0 ELSE sum([profit]) END

However, the Subtotal row on my report applies the same logic…and, since at the subtotal level sum(Sales) > 0, then all profit values are totaled and I get 55 rather than 45.  Basically, my need in Excel terms is to have each cell above show profit or zero depending on Sales, then sum the values above it for t he Subtotal.  Is there a way I can control how Subtotals are presented in this way?

• ###### 1. Re: Subtotal needs to sum what is displayed, not what underlies

Hi Dave,

Currently, there is no way to determine how a subtotal or grand total is computed. It will use whatever aggregation is on the measure, as you have found. The best way around this is to find the desired values and placed them on a dashboard with the detailed sheet. This is a good one for the ideas section, if it's not already there!

-Tracy

• ###### 2. Re: Subtotal needs to sum what is displayed, not what underlies

There are methods to achieve what you are looking for. Although, it does require an understanding of how Tableau computes with table calculations.

See the attached for an example.