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

    Percentage of Totals

    margaret.tanutama

      % 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?

      Screen Shot 2016-01-27 at 4.14.08 PM.png

        • 1. Re: Percentage of Totals
          Steve Mayer

          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
            margaret.tanutama

            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
              Steve Mayer

              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
                pooja.gandhi

                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
                  margaret.tanutama

                  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
                    pooja.gandhi

                    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