13 Replies Latest reply on Nov 3, 2015 12:55 PM by Jennifer Sanchez

# Group Grand Total and Subtotal?

Hi All -

As always - I appreciate anyone's time and help with this. I am trying to get a group grand total when I have sub totals. I can get the following information in tableau with the field names: Med Name, Status, and COUNT OF Med Name. But I would like to see the grand total in another column which in this case would be 2,774 (sum of each of the sub groups). I have multiple rows of "med names" so I cannot use "sum of records." I've also attached this in an excel spreadsheet with the column highlighted with how I would like tableau to calculate this for me so that I can do some further manipulation (% and per 1000 count). Any ideas would be greatly appreciated! Thank you all in advance.

Med Name                     Status          COUNT OF MED NAME

Cough Medicine              B                         50

C                          1

D                          1356

G                           62

I                             7

M                           17

N                            2

P                            1276

T                             3

• ###### 1. Re: Group Grand Total and Subtotal?

This should work (I hope )

{FIXED [Med Name]:SUM([Count of Med Name])}

• ###### 2. Re: Group Grand Total and Subtotal?

This should work (attached workbook)

All I did was create an LOD (Level of Detail) calculation: {Fixed [Med Name]:sum[count of Med Name]} - but I did prep in Excel slightly - I made sure that the med name was copied down next to all of the status' to be sure that it grouped correctly in Tableau.

Hope that helps!

-Ann-

• ###### 3. Re: Group Grand Total and Subtotal?

Thanks so much Ann, I should've clarified that I got the "count of med name" once I brought in the "status" field from tableau, the data is not set-up like that in the original excel file. When I do make that into an actual formula "count(med name)" and then use the  {Fixed [Med Name]:sum[count of Med Name]}" formula it gives me the error: "argument to SUM(an aggregate function) is already an aggregation and cannot be further aggregated." I'm guessing that's what's causing the issue. Does that make sense?

• ###### 4. Re: Group Grand Total and Subtotal?

Daniel :-) Thank you! I never do quite explain myself good enough so I should've mentioned that the  "count of med name" field came once I brought in the "status" field into tableau, the data is not set-up like that in the original excel file. When I do make that into an actual formula "count(med name)" and then use the  {Fixed [Med Name]:sum[count of Med Name]}" formula it gives me the error: "argument to SUM(an aggregate function) is already an aggregation and cannot be further aggregated."

I can try to mock something up in tableau if that is easier.

• ###### 5. Re: Group Grand Total and Subtotal?

Hi Therel, I mocked up a workbook. I want to get exactly the same thing as was suggested by both of you, a grand totals based on the total by med in a column right next to the med count by status. So Acetaminophen would have a grand total of: 11, And Aspirin would have a grand total of 1, etc.

Does that make more sense?

• ###### 6. Re: Group Grand Total and Subtotal?

Remove the SUM from the LoD calc.  You might need to wrap the whole thing as a sum but try it without.

• ###### 7. Re: Group Grand Total and Subtotal?

Like this:  {FIXED [Med Name]:COUNT([Med Name])}

• ###### 8. Re: Group Grand Total and Subtotal?

So I got somewhere with this..... I can get the grand totals at the bottom (yay) but I need to take the grand total for each med and divide that by each "status" to get a per 1000 count which I would want in the column right next to the med count.... for example

Acetaminophen: "B" status = 2 & grand total = 11

Per 1000 would be: 2/11*1000

• ###### 9. Re: Group Grand Total and Subtotal?

Here you go...

SUM({FIXED [Med Name],[Status]:COUNT([Med Name])})/SUM({FIXED [Med Name]:COUNT([Med Name])})*1000

• ###### 10. Re: Group Grand Total and Subtotal?

Thank you SO much Daniel! That seems to work out perfectly on my sample data! You really have helped so much! When working with my data the #'s don't seem to quite work out but i'm hoping theres something clearly logical as to why not and that i'll figure that out soon! :-) Maybe my filters are throwing it off somehow. TY SO MUCH! I look forward on updating you and saying that I figured it out!

• ###### 11. Re: Group Grand Total and Subtotal?

Yep! It's the filters that I have that throw off the numbers. I removed them all and the Per 1000 calc is spot on. Do you know why having filters would throw off the numbers? One of my filters is the med name field (some of the meds we don't want to include in the count)...and other filters also help to exclude specific medications....I wonder if the FIXED part of the med name in the formula impacts the Per 1000 scan and the filters?? hmmmm?

• ###### 12. Re: Group Grand Total and Subtotal?

You can put the filters you're using into context.  The LoD calc will calculate after the context filters.  This KB explains it:  Filters and Level of Detail Expressions

To put a filter into context just select the caret down and you'll see the option:

1 of 1 people found this helpful
• ###### 13. Re: Group Grand Total and Subtotal?

You're an absolute GENIUS!!!!!!!!!!!!!!! WOW.....THANK YOU SO MUCH!!!!!!!!!