8 Replies Latest reply on Apr 22, 2015 2:26 PM by Joe Oppelt

# Percent of Grand Total (field is table calculation)

Hi,

Pleasae see the attached example.

I am trying to add a column titled Perect of Grand Total for the Start to Mail Date field.

I'd like a third column in each region's section (six regions, need a third column in each) to show what percent each region's Start to Mail Date is of the Grand Total Start to Mail Date percent.

More explaination on the attached. Very much appreciate any suggestions as we've been trying to figure this out for too long!

Angie

• ###### 1. Re: Percent of Grand Total (field is table calculation)

I'd play with it, but I would need a working example to play with.

You'll need to do a table calc to get the grand total (52%) going across, and another to compute against it.  Need to mess with telling tableau how to partition and address the table calcs, perhaps where to restart, etc.

Hack up something similar (without confidential data) in an excel file and swap data sources to use that.  Then post that workbook.

See detailed steps I outlined for amother person in this thread:

Re: Linking dates from calendat table to other tables

• ###### 2. Re: Percent of Grand Total (field is table calculation)

Joe,

Thank you. Attached is the workbook with most fields hidden. Would appreciate any magic that you can work.

Thanks,

Angie

• ###### 3. Re: Percent of Grand Total (field is table calculation)

See Calculation2 in attached.

I did a window_sum of the row (restart every [Asset Range]) and then divide the sum of each Region by that window_sum.

To force it to restart every asset range, right click on Calculation2 in the Masure Values shelf.  You can mess with the calculation directions there.  By default, Tableau worked this calc running DOWN the sheet.  That ends up giving you percentages within Atlanta for each asset range.  Not what you need.  Telling it to run across the sheet makes tableau sum up each row across (that's what WINDOW_SUM does) and then calculates for each region.  So I selected Table(across) for the table calc definition.  (Note, there are times that you need to tell it to take multiple dimensions into account, and that's what the ADVANCED option is for.  You don't need it here, so no need to confuse things by explaining its use.)

I added the sum of [Days to Start] on the sheet too, just to demo the calc I did.  (Atlanta:  809/6934 = 11.7%)  I'm assuming that's the equation you really need there.  A percent of a percent isn't usually meaningful.  A percent of totals is.

If that's not what you are looking for, we can modify this to get the equation you need.

• ###### 4. Re: Percent of Grand Total (field is table calculation)

PS:  I modified the number format of Calculation2 to show 2 decimal places.  Otherwise it just showed zero for all the calcs.  You can show more decimals, format it to be a percentage, etc.

1 of 1 people found this helpful
• ###### 5. Re: Percent of Grand Total (field is table calculation)

Thanks Joe!

I might be able to get what the client wants using what you did. Greatly appreciate it!

They do really want the percent of the grand total percent (so apercent of a percent). So Atlanta would be like 118% and so on.

• ###### 6. Re: Percent of Grand Total (field is table calculation)

Looked at it again.

Tableau is gwetting the 52.5 by doing (window_sum(sum([Days Start to Mail]))/window_sum([Count of Exams]))

So your calc would be:

AVG([Days Start to Mail])/(window_sum(sum([Days Start to Mail]))/window_sum([Count of Exams]))  You get 1.18 for Atlanta that way.

• ###### 7. Re: Percent of Grand Total (field is table calculation)

Awesome!

Thanks a bunch.

Hope to meet you at TC in Vegas..??!!

Angie

• ###### 8. Re: Percent of Grand Total (field is table calculation)

Hope to be there.