7 Replies Latest reply on Oct 22, 2018 6:30 AM by Jalen Asperger

    Calculate Relative to Column Total

    Jalen Asperger

      Hello,

       

      I am attempting to use a column total (table calculation) as part of another calculation. Basically, I want to use a calculation to be able to color cells relative to the column total for that row. So if column one has a percentage of 5.88% and column two has a percentage of 0.72% and the overall total for that row is 3.28%, I want to use a calculation to subtract the column percentages from the total and use that to color the cells. So the calculation would result in the column one value of 5.88% to be colored with a value of 2.6% (5.88% - 3.28%) and the column two value of 0.72% would be colored with a value of -2.56% (3.28% - 0.72%). I'll attach a packaged workbook to help explain things better.

       

      Basically I just want to create a calculation to subtract the total column percentage for each row from the column percentage of the cells of each row.

       

      Hopefully that explains things, but let me know if I can provide any more information.

       

      I'm using Tableau 2018.1.1.

        • 1. Re: Calculate Relative to Column Total
          Shinichiro Murakami

          Hope this helps.

           

           

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Calculate Relative to Column Total
            Jalen Asperger

            Thanks, Shin.

             

            This is close, but not quite what I'm looking for. It appears the percentage delta you have calculated is the delta between the cell percentage and the average percentage for each column group. I want the delta to be between the cell percentage (i.e. 37.25% for the <=17, COD # 105 cell) and the grand total column percentage for that row (which is 20.08% for COD # 105). So for that cell, I want the delta to be 17.17%.

             

            Hopefully this explains what I want:

             

            Desired Delta.PNG

             

            Thanks for helping!

            • 3. Re: Calculate Relative to Column Total
              Shinichiro Murakami

              Revised

               

              Code average needs to be calculated by different way .

              Not the average of rate, but as static value on respective Code#.

               

              Thanks,

              Shin

              • 4. Re: Calculate Relative to Column Total
                Jalen Asperger

                Thanks Shin.

                 

                This is exactly what I wanted. However, I'm encountering an issue with the calculations in my primary workbook. In it, I'm using a parameter rather than the COD variable, which I don't think poses an issue, but something somewhere is causing an issue with the calculation.

                WrongPercentages.PNG

                 

                 

                One would expect the percent delta to be 0 for all the grand total values, but that is not the case. In some cases where I would expect a large delta (like 18%), I'm getting a small delta (like 2%), like in the picture above.

                • 5. Re: Calculate Relative to Column Total
                  Shinichiro Murakami

                  Because the calculation contains LOD fixed,. if your worksheet contains any filters, you need to change the filters to context.

                   

                  Filters and Level of Detail Expressions

                   

                  Create Context Filters

                  To create a context filter, select Add to Context from the context menu of an existing categorical filter. The context is computed once to generate the view. All other filters are then computed relative to the context. Context filters:

                  • Appear at the top of the Filters shelf.
                  • Are identified by a gray color on the Filters shelf.
                  • Cannot be rearranged on the shelf.

                  As shown below, the Ship Mode dimension is set to be the context for a view. The Region filter is computed using only the data that passes through Ship Mode.You can modify a context filter by:

                  • Removing the field from the Filters shelf – If other context filters remain on the shelf, a new context is computed.
                  • Editing the filter – A new context is computed each time you edit a context filter.
                  • Selecting Remove from Context – The filter remains on the shelf as a standard filter. If other context filters remain on the shelf, a new context is computed.

                   

                  Thanks,

                  Shin

                  • 6. Re: Calculate Relative to Column Total
                    Jalen Asperger

                    Do I need to redo the calculations or anything after adding the filters to context? Even if I add all the filters to context, I get the same results as before, with the grand total deltas not being 100%.

                     

                    Edit: Not sure if this helps, but for each of the rows, the delta results are summing to 0 in most cases, but not all. So if I set the parameter to one with two values, the delta for value one may be -0.023 and the delta for value two will be 0.023.

                    • 7. Re: Calculate Relative to Column Total
                      Jalen Asperger

                      Does anyone else happen to have any input on this? Changing the filters to context did nothing to change the final results that I was previously getting. The deltas for the grand total columns are still not 0% and the deltas for each of the cells is still incorrect despite following Shin's calculations above. The calculations work in the provided example workbook, but not the primary workbook I am working on.

                       

                      Calculation for column percentage seems to be working as expected:

                      Percentage Column.PNG

                      Calculation for grand total percentage(?) does not appear to be working as desired:

                      Percentage by Group.PNG

                      Calculation for delta is not working as desired, I assume because the grand total percentage calculation isn't working as expected:

                      Percentage Delta.PNG

                       

                      Any help is appreciated!