9 Replies Latest reply on Oct 30, 2014 9:12 AM by Anders Lam

# Subtotals on a calculated field

Hi,

I have reviewed the three steps in Jonathan Drummery's series on   Grand Totals and Subtotals | Drawing with Numbers but I can't still figure out how to correct this.  For reference, I have mocked up a twbx and kept it pretty simple.  I have one field (Planned for WP) which is the average of the results.  I have a second field which is the sum of the actuals.  I created a variance column which is (Avg Planned for WP) minus (Sum of Actual).  This works for each row but not at the subtotal and grand total level.

Can someone guide me on how to fix it?  The Subtotal for the two individuals rows are correct.  I would like it to be the subtotal for "Planned for WP" minus "Actual" which would lead me to the correct result.  Or it could be the sum of the calculated variances for each of the line items.  Either would work but I can't get Tableau to work this way.

Thanks,

Anders

• ###### 1. Re: Subtotals on a calculated field

Is this the correct output?

1 of 1 people found this helpful
• ###### 2. Re: Subtotals on a calculated field

If you change the Variance calc to "Total Using>>SUM" you'll get the following results:

Is that what you're after?

1 of 1 people found this helpful
• ###### 3. Re: Subtotals on a calculated field

That worked.  I should have tested out my mock up of data first.

In my real data set, the "sum" option is grayed out.  Any idea why?

• ###### 4. Re: Subtotals on a calculated field

Difficult to say without seeing the calculation or being able to interact with the workbook.

• ###### 5. Re: Subtotals on a calculated field

Here is a link to my original database:

In the "Root Cause Var" tab, you can see the 3 fields Planned Labour, Actual Labour and Labour Variance.  I can't use the sum function on Labour Variance.

Thanks for looking at this by the way.  Its been frustrating me for a couple of days.

• ###### 6. Re: Subtotals on a calculated field

I'm not exactly sure off the top of my head, so let me ping Jonathan Drummey to get his input on this.

BTW I've never seen a TWBX uploaded that way - how did you do that?

• ###### 7. Re: Subtotals on a calculated field

....I'm sorry, it's probably due to the fact that the calculation is using data from two blended data sources.

1 of 1 people found this helpful
• ###### 8. Re: Subtotals on a calculated field

I'm going to guess it's that issue as well.

1 of 1 people found this helpful
• ###### 9. Re: Subtotals on a calculated field

Thanks for the input!