6 Replies Latest reply on Dec 6, 2018 11:25 AM by androo

    Different Aggregation Types

    androo

      I have 100 records in a data set, and these records will fall into one of 3 buckets (A, B, C).

       

      I would like to display the data in a worksheet (looking for a worksheet solution, not a dashboard) so that beneath each bucket a percentage to total records is shown (as a percent), but for the grand total, I would like to see 100 (the total number of records).

       

      EXAMPLE:

                               A          B          C          Grand Total

      Project          25%     40%     35%          100

       

      What is the best way to do this?

       

      Thanks.

        • 1. Re: Different Aggregation Types
          Ritesh Bisht

          Create 2 sheets with % and Grand Total.

           

          Drag  both to the dashboard side by side

           

          Thanks,

          Ritesh

          • 2. Re: Different Aggregation Types
            androo

            Thanks, Ritesh. I am aware of the dashboard solution. I am hoping to find a solution strictly within the worksheet itself, if possible.

             

            I tried some SQL manipulation, but I am not sure how to split the logic using Tableau's calculation language.

            • 3. Re: Different Aggregation Types
              Ken Flerlage

              This is tricky...There may be a better solution than this, but I'll give it a shot. You could create two calculated fields--one that shows a % if it's not a grand total, otherwise shows NULL. And one that shows a SUM if it is a grand total, but otherwise shows a NULL.

               

              Not Grand Total

              // If only one segment, then assume this is a regular total...

              // ...for a single state and segment, so calculate % of sales.

              IF COUNTD([Segment])=1 THEN

                  // Single segments, so assume note a grand total.

                  SUM([Sales])/SUM({FIXED [State]: SUM([Sales])})

              END

               

              Grand Total

              // If has multiple segments, then assume this is a...

              // Grand total, so just display total sales.

              IF COUNTD([Segment])>1 THEN

                  // Multiple segments, so assume a grand total.

                  SUM([Sales])

              END

               

              Then you can drag those both to the Text card.

              Unfortunately, this falls apart if you have one item that has 100% of it's data in only one of the categories as shown in Wyoming above.

               

              It certainly feels like there should be a better solution for this.

              • 4. Re: Different Aggregation Types
                Don Wise

                Hi Androo,

                Here's an approach (attached 2018.3 workbook using Superstore data).  Tableau is fairly rigid with tabular data so this is as good as I could get it for you; if you can live with totals to left side, this might be an idea:

                Screen Shot 2018-12-06 at 9.45.47 AM.png

                • 5. Re: Different Aggregation Types
                  androo

                  Cool concept. I will try to see if it works in my situation. I will likely have buckets with 0 values from time-to-time, but this may be fine.

                   

                  I also thought this was something that should have a simple solution.

                   

                  Thanks, Ken!

                  • 6. Re: Different Aggregation Types
                    androo

                    I think I have something...

                     

                    I use a UNION ALL concept where I union the data set to itself and assign a [flag] (Source/Copy) to designate the original from the duplicate records.

                     

                    Here's the formula I use to achieve the split I desire (still have to work with formatting):

                     

                    IF ATTR([flag])="Copy" THEN

                        -WINDOW_SUM(SUM([Number of Records]), 0, IIF(FIRST()==0, LAST(), 0))

                    ELSE

                        SUM([Number of Records]) / SUM({ FIXED [flag]="Copy" : SUM([Number of Records]) })

                    END

                     

                    Essentially, the Copy records are used to generate the total, while the Source records are used for the category breakouts.

                     

                    I created a custom header to accommodate this using:

                     

                    IF [flag]="Source" THEN STR([StandardHeader]) ELSE "Total" END

                     

                    Notes:

                    In my application, I have additional items that may be included in the LOD denominator formula, but this is the simplified concept.

                    I still need to try the other suggestions to see if they also solve the problem.

                     

                    Formatting (edited):

                    I added a * (-1) to the "Copy" section of the formula above (shown in red).

                    Having the data on different sides of zero, I use the following custom number format to print the data in percentages and integers: #,##0%;#,##0

                    Also, because the values are on opposite sides of 0, I can use a 2-Stepped Custom Diverging Palette to display the Total as a darker (or different) hue than the buckets by setting the center as 0.

                     

                    Thanks!