3 Replies Latest reply on Jan 10, 2017 8:10 AM by Shinichiro Murakami

    Adding sub-totals in crosstab horizontally

    Vamsi Immadisetty

      Hi Group, I am using tableau desktop 10.2. I have a cross tab table with different measures,percentages and derived calculations.

      I have column level calculations like Col 1 = Col 12+Col11 and Col2 = Col14/Col13. When I try to add sub totals to this cross tab tableau adding all values in respective column and displaying total at the bottom. But my requirement is the subtotals should also populate values by following the column level calculations. When I work with Cognos we used to deal these things by using  Solve Order functionality. Do we have similar kind of functionality to display column level calculations in sub totals. Attached is the reference doc for same.

       

      In the attached example Row six refer to Indicator Total is represent sub totals, but the values are calculated horizontally similar to other rows.

      like B6 = G6/F6 and C6 = H6/I6

       

      Pleas suggest me how to achieve this in tableau. 

       

      Thanks-Vamsi.

        • 1. Re: Adding sub-totals in crosstab horizontally
          Shinichiro Murakami

          Hi Vasmi

           

          In this case, you can add column total with aggregated calculation.

          You can  get same %age in each indicator when you use

          [Col 12] / [Col 11]

          But Total will be sum of % by this formula.

           

          [Col 1 : Col 12 / Col 11]

          sum([Col 12])/sum([Col 11])

           

           

          [Col 2 : Col 14 / Col 13]

          sum([Col 14])/sum([Col 13])

           

           

          [Col 6 : Col 10 / Col 9]

          sum([Col 10])/sum([Col 9])

           

          [Col 7 : Col 13 / Col 11]

          sum([Col 13])/sum([Col 11])

           

           

          Thanks,

          Shin

          • 2. Re: Adding sub-totals in crosstab horizontally
            Vamsi Immadisetty

            Hi Shin, thanks for the reply . It worked very well. Thank you very much. It saved my time.

             

            I have one more issue with formatting, will you be able to help me to fix that.

            I have a simple crosstab with five columns and ten rows. I wanted to give different colors to different column headers.

            Let say Column 1 header background color should be Blue,Column 2 header background color should be Red,Column 3 header background color should be Yellow.

            There is no condition behind these colors coding. I know it is basic question but I am not able to do that in tableau.

            The movement I select column and change color it is getting apply to entire work book (all columns in header).How to deal with this, any help will be appreciated.

            COL1,COL2 background in Light Blue color

            COL 6 and COL 7 background in Yellow color

            and other columns in Blue Color.

             

            Thank you,

            Vamsi

            • 3. Re: Adding sub-totals in crosstab horizontally
              Shinichiro Murakami

              Vamsi,

               

              I know your request is quite simple and many people want this function.

              But actually, this is the one of the most difficult thing to be done in Tableau.

              Tableau is very good at creating interactive charts, but in my personal opinion, not good at to create customized table view.

               

              That's typically requested from the perspective of creating the similar view from Excel + PPT type presentation material.

              I don't necessarily think Tableau is not good because of missing of these table view, becasue it has many other very good feature,

              but it is true this is one of disappointing point.

               

              Only the way I know of is using dashboard like below, but this requires very troublesome width adjustment across two sheets and complicated format.

               

               

               

               

              Thanks,

              Shin

              1 of 1 people found this helpful