10 Replies Latest reply on Jul 16, 2015 7:07 AM by Rody Zakovich

    How to add Column "% Total"

    phuviet.pham

      Hi everyone,

       

      I have the Exel file as the Attachment. I have already created Column "Total" for Sale Category A,B and C. Now i want to have Column "% Total" beside the Column "Total" like the Exel file.

      I have read some Calculations but there are still not correct Answear. Can you please help me ?. Thanks

        • 1. Re: How to add Column "% Total"
          Rody Zakovich

          Hello Phuviet,

           

          An example workbook would be beneficial here.

           

          Is A, B and C in one Column? Or are they 3 separate columns?

           

          What version are you on? If 9.X, then we could use LoDs, if not we can use a combination of Window Calcs.

           

          Regards,

          Rody

          • 2. Re: How to add Column "% Total"
            phuviet.pham

            Hi Rody,

             

            I want to create a WorkSheet whick Looks exactly the same as the Exel File.

            • 3. Re: How to add Column "% Total"
              Rody Zakovich

              Hello Phuviet,

               

              I understand, but how you accomplish this depends on the data structure, and which version you are on.

               

              If A, B and C are different columns, than you can create a calc like 

               

              SUM(ZN(A) +ZN(B) + ZN(C) )

               

              To get Row Totals.

               

              Then, if you are on V9, you could create a calc like

               

              SUM(ZN(A) +ZN(B) + ZN(C) ) / { SUM(ZN(A) +ZN(B) + ZN(C) )  }


              To get the % of Totals, but if you are on V 8.3, you'll have to use a window calc.


              If A, B and C are in the same column, then things change slightly.


              Regards,

              Rody

              • 4. Re: How to add Column "% Total"
                phuviet.pham

                Hi Rody,

                 

                A,B,C are in different Columns. I know how to calculate % Total. But I dont know how to place the Column "%Total" beside the Column "Total" in Tableau?

                 

                Its difficult to make it with Tableau

                • 5. Re: How to add Column "% Total"
                  Rody Zakovich

                  Hello Phuviet,

                   

                  I can get you to this

                   

                  7-16-2015 9-32-46 AM.png

                   

                  But I don't think this.

                   

                  7-16-2015 9-33-42 AM.png

                   

                  Is possible in Tableau using a single sheet. The only way we can add a "Row" into a crossbar using Tableau us using the Row and Column Totals in Analysis. But here, you are essentially trying add two Totals, which (To my knowledge) we cannot do.

                   

                  Regards.

                  Rody

                  1 of 1 people found this helpful
                  • 6. Re: How to add Column "% Total"
                    pooja.gandhi

                    Phuviet,

                     

                    Doing things in Tableau that look like excel defies the use of a BI tool like Tableau. However, if you want it to look like excel you need to do some calcs.

                     

                    You can create a calc and name it Total: ZN(sum([A]))+ZN(sum([B]))+ZN(sum([C]))

                    Then create a % total calc and name it % Total : [Total]/total([Total])

                     

                    Change the default properties of the % total calc to reflect 2 decimal percentage. If you double click the new calcs they are brought onto the view. That takes care of your first requirement to bring % total calc next to the total.

                     

                    To bring the % total calc on the row, I am not aware of a single sheet method. You can however create a window calc in the new sheet and bring the resulting sheets on to a dashboard to disguise Tableau to look like your excel file.

                     

                    You will need 3 window calcs for each of your measures A, B and C like window_sum(zn(sum([A])))/total([Total])

                     

                    See attached version 8.3 WB!

                     

                    Capture.PNG

                    1 of 1 people found this helpful
                    • 7. Re: How to add Column "% Total"
                      phuviet.pham

                      Hi Pooja and Rody.

                       

                      Thanks for Answears. i have thought to create a second Worksheet for Row % Total also. But the Problem is that. When we filter some Data, the first Worksheet  with less Data will be not near the Worksheet for Row % Total as you did. And The Dashboard Looks not very nice.

                      So i will have to think of another Solutions.

                      • 8. Re: How to add Column "% Total"
                        Rody Zakovich

                        Hello Phuviet,

                         

                        Since you are using these sheets on a Dashboard, you could Apply the Filter to both Sheets, this will ensure both are filtered together.

                         

                        To ensure the sheets are kept in line, Create a placeholder Text field to put into the Column shelf of the second sheet.

                         

                        % Total =>

                        ' % Totals '

                         

                        7-16-2015 10-01-15 AM.png

                         

                        Then you could place the two sheets within a Vertical Container.

                         

                        7-16-2015 10-00-52 AM.png

                        Regards,

                        Rody

                        • 9. Re: How to add Column "% Total"
                          phuviet.pham

                          Oh, Thanks Rody,

                           

                          I have forgot to use Vertical Container. It works

                          • 10. Re: How to add Column "% Total"
                            Rody Zakovich

                            Adding the Column Placeholder will give both sheets the same relative Width. So you can ensure the % total sheet stays in line with your Columns from the Crossbar Sheet.

                             

                            Using a Vertical Container will ensure the % Total Sheet moves up, as you filter Dates from the Crossbar sheet.

                             

                            Ignore the numbers, I didn't want to fix the LoDs for filters, but I wanted to show you that it works.

                             

                            7-16-2015 10-07-20 AM.png

                            Regards,

                            Rody

                            1 of 1 people found this helpful