8 Replies Latest reply on Feb 16, 2017 4:20 AM by Thalita Kadlec

    Adding a Percentage column only for row Totals

    Thalita Kadlec

      Ok, so I've found some similar topics around but none of which provided a solution the way I need it.

      All the topics I found were full of tricks and complicated calculations to do such a simple thing.

       

      So I have a table showing # of training hours by Category per quarter.

      Exemple:

       

      Category     1 Quarter          2 Quarter          3 Quarter          4 Quarter          Total

      A                    60                         40                    50                    70                   220              

      B                    90                         10                    30                    80                   210

      C                    100                       20                    70                    50                   240              

      Grand Total     250                     70                    150                 200                  670

       

      So, I need to add a new column after the "Total" column showing the % from the grand total, like that:

       

      Category     1 Quarter          2 Quarter          3 Quarter          4 Quarter          Total          % of Grand Total

      A                    60                         40                    50                    70                   220               33%

      B                    90                         10                    30                    80                   210              31%

      C                    100                       20                    70                    50                   240               36%

      Grand Total     250                     70                    150                 200                  670               100%

       

      The only way I got any where near this was by creating a new Sum(Hours) pill formatted to be shown as % and dragging it into the table, however this results in 5 new % columns (one for each quarter + 1 for the totals) and I only want the totals % column.

      I'm kind of perplexed that Tableau won't let us do this easily as this is such a common necessity for tables and data display.

      So? Any suggestions?

        • 1. Re: Adding a Percentage column only for row Totals
          Khang Pho

          Hi Thalita,

           

          One solution would be to create a separate view that has the grand total as a percent of total.  Then put the two view next to each other as a dashboard so it will simulate that look.  In the % total view you can hide headers so the categories will not show.

           

          Regards,


          Khang

          1 of 1 people found this helpful
          • 2. Re: Adding a Percentage column only for row Totals
            John Sobczak

            One way to do this is duplicate the sheet and remove the date dimension from columns and then make the measure a quick table calc % of total.  Then line both sheets up next to each other like below.

             

            2-15-2017 10-03-27 AM.png

            3 of 3 people found this helpful
            • 3. Re: Adding a Percentage column only for row Totals
              Simon Runc

              I'm kind of perplexed that Tableau won't let us do this easily as this is such a common necessity for tables and data display

              ... you've answered your own question! - Tableau is a data visualisation tool and not for displaying data in table form. The (general) way Tableau works is to make the stuff you do (assuming you are doing data viz!) 80% of the time really quick and easy, and a bit more work for the other 20%. I'm happy it's this way round! - To allow it to quickly do what you need, would break the whole "pill" and "level of detail" thing. Yes it would let us build this table more easily, but a regular bar chart would take longer!!

               

              Rant over!!...I have a solution for you! (and it's not too bad!)

               

              So one way to do this, is to create the calculations separately, and this way we don't need to use Quarter on the Shelf, so we only get the columns we want.

               

              So first I create a calculated field to pick up the quarter number...

              [Order Date - Quarter]

              DATEPART('quarter',[Order Date])

               

              btw this is only the DATEPART, so you'll need to filter to the year you want to show

               

              I can then use this to create 4 separate calculated fields for each for each Quarter

              [Sales - Q1]

              IIF ([Order Date - Quarter]=1,[Sales],NULL)

               

              [Sales - Q2]

              IIF ([Order Date - Quarter]=2,[Sales],NULL)

               

              ...

               

              [Sales - Q4]

              IIF ([Order Date - Quarter]=4,[Sales],NULL)

               

              I can then bring these in (using the Measure Names and Measure Values that Tableau creates), along with the original Sales field (this is the total sales). I then bring it in again, and use the "Quick Table Calc" on % of Total to create a %age on the total. I then show the Column Grand Totals

               

              Hope that helps, and makes sense...let me know if not

              3 of 3 people found this helpful
              • 4. Re: Adding a Percentage column only for row Totals
                Gerardo Varela

                Hi Thalita,

                     Here is a thread that has another way of doing it.  I've tried explaining everything in the workbook I attached in the other thread.  Hopefully it all makes sense! 

                 

                Calculate percentage of total for just grand totals

                 

                Regards,

                Gerardo

                3 of 3 people found this helpful
                • 5. Re: Adding a Percentage column only for row Totals
                  Thalita Kadlec

                  This works to some extent however I am dealing with huge tables that don't fit on the screen without scroll bars, and this solution ends up creating two scroll bars, one for each table, so it ends up very messy, because if I scroll on the first table, the second one with the % will need to be scrolled too, or the data won't make sense.

                  Thanks anyways!!

                  • 6. Re: Adding a Percentage column only for row Totals
                    Thalita Kadlec

                    This works to some extent however I am dealing with huge tables that don't fit on the screen without scroll bars, and this solution ends up creating two scroll bars, one for each table, so it ends up very messy, because if I scroll on the first table, the second one with the % will need to be scrolled too, or the data won't make sense.

                    Thanks anyways!!

                    • 7. Re: Adding a Percentage column only for row Totals
                      Thalita Kadlec

                      Simon, I confess I was a bit annoyed yesterday because I had been trying to solve this for a long time and nothing seemed to work and usually Tableau is so intuitive and easy to use.

                       

                      I agree that Tableau is a data visualization tool but in order to achieve a good visualization of your data you do need in the first place well structured tables/data bases. So yeah I still find it annoying that such a simple need for a table is so non-intuitive to build on the software! Haha.

                       

                      Well, I will try your solution, let's see what comes out of it.

                      Thanks once again!! You have been very helpful!

                      • 8. Re: Adding a Percentage column only for row Totals
                        Thalita Kadlec

                        Gerardo, thanks a lot for your input.

                        I will try that too.