4 Replies Latest reply on May 16, 2018 11:46 AM by Max Giegerich

    Percent of Table Calculation

    Justin Woodruff

      Good afternoon,

       

      I'm working on a visual that has two stacked graphs. Right now they are side by side and then I will make them dual and then synchronize the axis. I'm doing this because I need two different labels: one needs to be the total for that time frame and the other needs to be the percentage of that over time. When I go to the analysis tab and do the percentage of table calculation, it does the formula for both graphs. Is there a way to prevent that from happening? I don't think so. As a result, i believe I need to create a calculated field. However, I'm struggling on what the formula needs to be. Can you please provide some help?

       

      Thanks,

       

      Justin

        • 1. Re: Percent of Table Calculation
          Deepak Rai

          Pl attach some workbook to help u.Use some fake data.

          • 2. Re: Percent of Table Calculation
            Max Giegerich

            Hi Justin,

             

            I have attached an example based on what I think you're asking; but please let me know if I've misinterpreted.

             

            If you add the measure to the label card first and make this the percentage (by right clicking the measure, quick table calculation, percent of total) then drag on the measure again Tableau will treat this second measure separately.

             

            So do the table calc first so that Tableau doesn't think it's a duplication of the field.

             

            Thanks,

            Max

            • 3. Re: Percent of Table Calculation
              Justin Woodruff

              Good morning,

               

              I have attached an example packaged workbook that better illustrates what I am trying to do. There are three worksheets here.

               

              1. The data labeled by the Number of occurrences.

              2. The data labeled by the percent of each year (% of table).

              3. I am trying to get both labels on one graph. I want the number to be on the right edge of each bar and the percentage to be on the very left of each bar. I want to combine the two and synchronize.

               

              Please assist.

               

              Thanks,

               

              Justin

              • 4. Re: Percent of Table Calculation
                Max Giegerich

                Hi Justin,

                 

                I have attached a workbook that has two examples of how I'd tackle this. Using measure names and measure values isn't going to work with table calculations unfortunately, so pivot your data set around the FY value as shown in the 'Pivot Example' data set. This will be available in Tableau if you're using a text or excel file as a data source. If not, hopefully you have a way of getting the data into this structure by restructuring the SQL query, etc.

                 

                Pivot Data from Columns to Rows

                 

                Once pivotted, I have provided two options.

                 

                1. Uses a reference line per cell with a label but not line (my preferred option).
                2. Dual axis with a placeholder field set to half the minimum value across the data set using an LoD calc..

                 

                Hope this helps!

                 

                Max