3 Replies Latest reply on Dec 3, 2018 11:16 AM by Paul Wachtler

    Adding Col & Row Percent Values to "Standard" Crosstab Using Grand Total via Analysis Menu

    Robert O'Connell

      Hello, I would appreciate some guidance on the best approach to replicating an Excel table that, at first glance, would appear to be straight forward.

      However, I have not seen this type of chart done within the tableau community sites not have I seen it in the forums or any other "how to" sites.

       

      My "customer" produces an Excel table in crosstab form. The image is below. The important point is percent values.

       

      - I can replicate that to the extent shown in the image below . Our rows are a buss line, column are a level of risk and the values are a count of records.

      - The Grand Totals come from using the Analysis>Totals> select grand totals for row and columns.

      - My problem is finding a way to extend this crosstab with the percentage value in both rows and columns as was done in the Excel version above.

       

      - So far I have experimented with LOD calc and Table calculations which show that I can at least get the total values (see image below) and presumable the percent by extending those calculations.

        However I don't think the column totals are entirely correct and this also this immediately inhibits the automatic grand total calculations for rows & columns that re correct in value & placement.

      - Nor  can I get these totals on the right side of the crosstab where they belong. Nor the column values to the bottom as needed. I have tried the measure values/names approach and that doesn't seem

        to solve my calculation "placement" issue.

       

      The more I "work" through it, the more it just doesn't feel like my approach is correct.   I know I can achieve this effect by row and table percentages as separate worksheets but trying to put them together in a dashboard

      seems like a rabbit hole with problems written all over it.

       

      So I would appreciate any "start from the beginning" guidance that would get me thinking correctly on how to approach. For example, is there a way to use the Tableau grand totals to compute percent values and have those display as additions to the associated rows/column? If not, what is the "best" way to do those calculations against a crosstab table structure that appears pretty basic  This type of table is used multiple times by the client so it is pretty important to

      try and set it up in an easily replicated structure.

       

       

       

      Thanks,

      Bob

        • 1. Re: Adding Col & Row Percent Values to "Standard" Crosstab Using Grand Total via Analysis Menu
          Paul Wachtler

          Hi Bob,

           

          Tableau isn't made for duplicating excel's functionality.  The only way to add that Pct column and Percent row will be to create them in separate worksheets and drag all three worksheets onto a dashboard where you can arrange them to look like that - like they're a single worksheet.

           

          If you attach a sample workbook I can help you work through doing this.

           

          Best,

          Paul

          • 2. Re: Adding Col & Row Percent Values to "Standard" Crosstab Using Grand Total via Analysis Menu
            Robert O'Connell

            Paul,

             

                 Thanks for your quick response.   I'm truly disappointed to understand that there isn't a magic approach that I had missed.

             

                 I also appreciate the offer to  show me how to build a the desired dashboard chart as three components. I wanted to be a little more proactive so before I sent a  workbook to work on, I thought I would try out an approach on my own.  Here is what I came up with and would appreciate you comments relative to the approach being correct or where it could be refined.

             

            Thanks,

            Bob

             

            Central Table is Crosstab with ROW/Column Grand Totals added

             

            Built the row percentages by Percent of Total Table (column down)  Calc applied to CNT(Model ID). I removed the left header information.

             

            The row percentages were similar except for using row across for the table calculation. Removing the header got me the chart shown below:

             

             

            My real question I brought these together, for right now, in a test dashboard but it looks like below.  Though what I have found is that bringing these together, especially the column percent, take a lot of finesse relative to positioning and labeling.

             

            • 3. Re: Adding Col & Row Percent Values to "Standard" Crosstab Using Grand Total via Analysis Menu
              Paul Wachtler

              Hi Bob,

               

              Looks good.  That's exactly what I meant when I said to build them separately and bring them together in a dashboard.  Unfortunately it does require a lot of finesse to make it look like a single table, but that's the only way to do it in Tableau.

               

              Best,

              Paul