5 Replies Latest reply on Sep 9, 2016 11:26 AM by Shinichiro Murakami

    Create Total as a Row

    Amit Naik

      Hello All

      I am trying to see whether we can add a row called Total which can be an aggregate of all Rows.

       

      For ex: In the Superstore Sample there are 3 entries in the Category Column : (1) Furniture (2) Office Supplies (3) Technology

      Now in the same Category Column  I need another entry called "Total" which is Sum of all 3 elements (Furniture;Office Supplies;Technology )

       

      I have tried Sets etc but its not working.

      Also I need to see if this a % Calculation then SUM rollup wont work. hence need Aggregate etc .. Any ideas ?

        • 1. Re: Create Total as a Row
          Amit Naik

          This is what the problem is. For ex: I am using the Profit Ratio calculation. I am able to put a row for Total using this technique. But when I make a chart of it then Total inst visible !

           

          (1) able to get have Grand Total

           

          (2) not able to see Grand Total on Charts

          • 2. Re: Create Total as a Row
            Shinichiro Murakami

            The easiest way to get Total on top of each category is that

            using 2nd axis to show Total.

             

            At first you will see 100 % overlaid picture, but you can remove color from 2nd axis.

             

             

            Sync Axis.

             

            Put measure name pill to color shelf and you can choose color

             

            and you can also change the name in the legend.

             

            This is sum of all profit / sum of all sales across all category with below formula.

             

            [Profit Ratio]

            sum([Profit])/sum([Sales])

             

            You can see that is aggregated value in the shelf with "AGG(**)".

             

            Thanks,

            Shin

            1 of 1 people found this helpful
            • 3. Re: Create Total as a Row
              jason.harmer

              The previous person was on the right track but a simpler version is to just do a dual axis. So for your example, you currently have 1 sum(sales) pill in the rows shelf. also, you have a category pill in the colors mark.....this is good for the first part.

               

              From there, duplicate sum(Sales) so you have 2 pills on the row shelf. then, make it go to a dual axis. note: sometimes Tableau will change the chart type to a bar or bubble once you create a dual axis. you can manually force them back to a line chart.

               

              Then, in the marks shelf...got to the 2nd sum of sales and make sure you do NOT have the category dimension on the color shelf, so it's only 1 color.

               

              tableauimp.jpg

              1 of 1 people found this helpful
              • 4. Re: Create Total as a Row
                Amit Naik

                Thanks Jason & SHINICHIRO. This did help but one small question. I do have a prompt which shall also show the Total.

                There is the ALL in the prompt but if I click that I only want the Total Line to be visible.

                 

                Once again, thanks for your kind help .. !!

                 

                 

                 

                • 5. Re: Create Total as a Row
                  Shinichiro Murakami

                  That requires complicated settings and I don't recommend it.

                  Instead, you can click color legend and highlight Total only.

                   

                  Shin