5 Replies Latest reply on Sep 22, 2017 6:18 AM by Jim Dehner

    Calculate total in 2D table

    Sha Cheng

      Hi,

       

      I have difficulties to calculate the total in a table when the data field is stretched to 2 dimensions. Please see example:

       

      I have raw data below, each sale contains a few items with the sales figure as a percentage out of the dollar amount of each sale.

       

      I have made a calculated field in tableau called ItemSales to provide the dollar amount for each item (Percentage * Sales), then I have this field in a 2d table with Country and Sector in each dimension.

      What I trying to achieve is to turn this to a percentage figure against the total ItemSales listed in this table, and what displays in the table is dynamic and controlled by the Sale Number and Country checkbox filters.

      However, I am struggling to calculate the total ItemSales in 2d, then can use it as a denominator to calculate the percentage.

       

      To illustrate the problem, I currently have Table 1, and I want to turn it into Table 2. It is as simple as show value as  "% of Grand Total" in Excel!

       

      I have attached the example tableau workbook (version 10.3). Thank you

        • 1. Re: Calculate total in 2D table
          Jim Dehner

          hi

          here is one way to do it

           

          I start by calculating the fixed sales for each Sector and country group - the formula is an LOD fixed expression that creates a permutation of the dimensions that appear before the colon (:) and uses the formula after the colon to aggregate the data

          in your case the formula is           { FIXED [Country],[Sector]:sum([Sales])}

           

          next we use this to create a percentage -             sum([ItemSales])/sum([fixed extended sales]) 

           

          the percentage and the fixed totals are applied at each row level - see below

           

          Then using the Tableau Grand total function the table totals are determined - the calcualtion for percentage is applied to the column totals

           

           

          Adding filters for sector and country provide cabability to limit the presentation per user selection while preserving the calculations

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Calculate total in 2D table
            Sha Cheng

            Hi Jim,

             

            Thank you for the reply. Your solution provides a percentage of the sales item against each sale they belong to, however it does not provide the percentage of each category out of the total sales figure. To illustrate, I currently have Table 1, and I want to turn it into Table 2 in tableau.

            Regards,

             

            Sha

            • 3. Re: Calculate total in 2D table
              Jim Dehner

              see attached

              just added a table calc as shown

              Jim

               

               

               

              • 4. Re: Calculate total in 2D table
                Sha Cheng

                Thank you Jim It does turn the numbers into percentage, however the figures are not correct. I.e. for country CAN and Sector Materials, there is only one record in raw data with ItemSales $20. The total sales on the table is the sum of the three sales: $50+$100+$75=$225, so the percentage should be $20/$225=8.89% instead of your result 16.67%.

                 

                Also I'd like to correct the percentage table (table 2) in my illustration earlier, sorry I didn't noticed it included the All line and made everything halved. The correct figures should be like below:

                • 5. Re: Calculate total in 2D table
                  Jim Dehner

                  These are the numbers in the model -

                   

                  I have done what I can for you

                   

                  Good luck with the solution you seek

                  Jim