6 Replies Latest reply on Jan 29, 2016 5:14 PM by pooja.gandhi

# Percentage of Totals

% of sub and grand totals. How do I simply create % of grand totals without combining 2 sheets to a dashboard? Can I create % of grand totals as easy as how I can do it in Excel?

• ###### 1. Re: Percentage of Totals

Margaret - please post a packaged workbook with sample data so that we can better help you out with this question.

Thanks,

-Steve

1 of 1 people found this helpful
• ###### 2. Re: Percentage of Totals

Steve,

Posted is another method Kent Sloan taught me. Can I do it on the same sheet with the grand total column and next to it, the % of grand total or sub total?

Thanks!

• ###### 3. Re: Percentage of Totals

Margaret - unfortunately, Kent's solution is probably how I'd approach this.

If anyone else out there has had success combining % of subtotals & totals, plus grand totals all on one worksheet, please chime in.

-Steve

• ###### 4. Re: Percentage of Totals

Having access to Tableau 9.0 or higher can make this a little easier because you are able to use LOD expressions:

If you break the weeks like this and create LODs, you can very well do this all on a single worksheet. But ofcourse, if you have more weeks, you are better off making weeks appear in columns outside of Tableau:

Week 1: IF [Week] = 'Week 1' then [Values] end

Week 2: IF [Week] = 'Week 2' then [Values] end

Total by Name: sum([Week 1]) + sum([Week 2])

% by Table: { fixed [Name], [Items] : sum([Values]) } / { sum([Values]) }

% by Pane: { fixed [Name], [Items] : sum([Values]) } / { fixed [Name] : sum([Values]) }

Name on columns, and double click on the 5 calculated fields to bring them on the view and turn on sub totals and grand totals and you're all set!

1 of 1 people found this helpful
• ###### 5. Re: Percentage of Totals

Pooja Gandhi, Thank you for the answer. A couple questions:

1. In the workbook, tab 'Calculated', how do I turn the numbers to show percentage? What is the Tableau function to convert numbers to function?
2. By "But of course, if you have more weeks, you are better off making weeks appear in columns outside of Tableau:", did you mean I should do like what's shown in the workbook, tab 'TFunction'? Either way, do you know how I can just show the % of the total column instead of the % of week 1 and week 2 columns?

Have a great weekend!

1 of 1 people found this helpful
• ###### 6. Re: Percentage of Totals

You can change the default properties of any field by right clicking on it and choosing > number format. Your tabs don't match the tab names you mentioned in this post. So I am not sure what I am looking at. For the way I suggested above to work effectively, you will need to break down the weeks or whatever datepart you choose to use. I meant that if you have a view that has more weeks, then breaking them the way I suggested could be cumbersome and you may be better off formatting the data outside of Tableau.

Cheers!

Pooja

1 of 1 people found this helpful