1 2 Previous Next 15 Replies Latest reply on Apr 25, 2011 5:10 PM by Richard Leeke

    Different Table Calc Context for SubTotals and Totals

    Michael Mixon

      I haven't been able to find anything on this, so I suspect it is not yet possible, but I wanted to let some of the forum wizards chime in.

       

      In the attached image, I have a typical Excel table that I would like to be able to replicate in Tableau.  Most of the subtotals are simply that...aggregations of the individual components.  The exception, though, is Mix.  For each of the products, I want to calculate the % of Total within each Segment...no problems there.  But when I display the sub-total of Mix by Segment, I want it to be the % of Total within the Region.  Right now, in Tableau, each Mix sub-total would just show 100%.

       

      Is there any way to accomplish this within a single table/chart?  I know that if I created two charts, one with product detail and one without, I could accomplish this, but then the subtotals would not fall directly beneath the product detail for each segment, but rather be grouped together in a separate segment sub-total chart/table.  Not always a big deal, but when trying to replace some existing Excel reports, this proves to be a sticking point with some of our users.

       

      Any assistance would be appreciated.

        • 1. Re: Different Table Calc Context for SubTotals and Totals
          Joe Mako

          Yes, I believe it is possible. Can you provide the Excel file that you used yo create that screenshot?

          • 2. Re: Different Table Calc Context for SubTotals and Totals
            Joe Mako

            Here is one method:

             

            Create three calculated fields:

             

            Segment Total

             

            TOTAL(SUM([Value]))


             

            Region Total

             

            TOTAL(SUM([Value]))


             

            Mix

             

            IF MIN([Segment])=MAX([Segment]) THEN
            
             IF MIN([Product])=MAX([Product])
            THEN SUM([Value])/[Segment Total]
            ELSE SUM([Value])/[Region Total]
            END END
            


             

            Place the "Mix" field onto the sheet, and bring up the pill's Edit Table Calculation dialog. You will see a drop-down titled Calculated field, with the two options "Segment Total" and "Region Total".

             

            - Set the Compute using for "Segment Total" to "Product"

            - Set the Compute using for "Region Total" to "Segment"

             

            This is done in the attached.

             

            If your situation is different, please provide a sample data file that represents your situation.

            1 of 1 people found this helpful
            • 3. Re: Different Table Calc Context for SubTotals and Totals
              Michael Mixon

              That's awesome, Joe.  Works perfectly.  So the Min/Max logic simply tells you whether you're on a detail row or a sub-total row?

              • 4. Re: Different Table Calc Context for SubTotals and Totals
                Joe Mako

                The Min/Max logic I used will work in this situation to determine where the calculation is being performed. Other situations, you may need a different calculation, or if there are situations where a Region has only one Segment, and the Segment has only one Product, then you will need additional logic to capture for those situations.

                • 5. Re: Different Table Calc Context for SubTotals and Totals
                  Michael Mixon

                  Is there a reason you use both Min and Max in the logic, when it appears that using "IF MIN([Segment])=MIN([Segment])" achieves the same outcome?

                  • 6. Re: Different Table Calc Context for SubTotals and Totals
                    Richard Leeke

                    This is an interesting one.  When I first saw the question my reaction was "not possible", but that's always a dangerous thing to say.

                     

                    The reason for the MIN/MAX logic is that Joe is using the fact that MN and MAX of a particular dimension are not the same to indicate that the current row is a (sub)total with respect to that dimension.  But as Joe says, that breaks down if there is only one value for that dimension at any particular place in the hierarchy.  So I would treat this approach with real caution - it would be very easy to build something that gives the right answer when you first test it, but proves to be susceptible to changes in data distribution over time.

                     

                    I had a good look at how all of the available functions work with respect to subtotals, and I can't find any reliable general way of doing what you want (but just because I can't think of it doesn't mean there isn't a way, of course).  I'll be interested in Joe's thoughts.

                     

                    If you look in the tool-tips, you will see that the values of the relevant dimensions display as "All" for the subtotal rows.  So if you could just get at that from the calculated field logic you would be able to do what you want.  But unfortunately there doesn't seem to be any way to get at that.  I think there's a need for an ISTOTAL() function to test whether or not you are on a sub-total row.

                    1 of 1 people found this helpful
                    • 7. Re: Different Table Calc Context for SubTotals and Totals
                      Joe Mako

                      "MIN([Segment])=MIN([Segment])" would always return true, so I don't know of a situation where that would be useful.

                       

                      "MIN([Segment])=MAX([Segment])" returns False when in the subtotal for "Segments".

                       

                      Richard, yes this is a simplistic test, and an ISTOTAL() function would be very nice for situations like this, but you still need a way to determine which subtotal you are in. I believe it is possible, but I still do not have a working solution for detecting total of a single row.

                      • 8. Re: Different Table Calc Context for SubTotals and Totals
                        Michael Mixon

                        Hi Joe,

                         

                        I'm attaching a revised version of your mix.twbx file that shows that MIN/MIN produces the same results as MIN/MAX.  It's possible I'm doing something wrong, but when I was deconstructing your initial post to make sure I understood it, I noticed that there didn't appear to be any difference between the two options, and that still looks to be the case.

                        • 9. Re: Different Table Calc Context for SubTotals and Totals
                          Joe Mako

                          The calculated fields are nested IF statements, so there are really 4 possible results, if you change:

                           

                           

                          IF MIN([Segment])=MIN([Segment]) THEN
                          
                               IF MIN([Product])=MIN([Product]) THEN "Yes" ELSE "No" END
                          END
                          


                          and

                           

                          IF MIN([Segment])=MAX([Segment]) THEN
                          
                               IF MIN([Product])=MAX([Product]) THEN "Yes" ELSE "No" END
                          END
                          


                           

                          to

                           

                           

                          IF MIN([Segment])=MIN([Segment]) THEN
                          
                               IF MIN([Product])=MIN([Product]) THEN 1 ELSE 2 END
                          ELSE
                              IF MIN([Product])=MIN([Product]) THEN 3 ELSE 4 END
                          END
                          


                          and

                           

                          IF MIN([Segment])=MAX([Segment]) THEN
                          
                               IF MIN([Product])=MAX([Product]) THEN 1 ELSE 2 END
                          ELSE
                              IF MIN([Product])=MAX([Product]) THEN 3 ELSE 4 END
                          END
                          


                           

                          You can see the possible values displayed in the attached.

                           

                          In this data 3 is never a result, but theoretically could be (this is one of those situations Richard brings up, if 3 is a result, then there are other complexities to deal with).

                           

                          Also notice how I am using a numeric measure instead of a text value, this is because text values cannot be displayed in a Grand Total or Sub Total.

                          • 10. Re: Different Table Calc Context for SubTotals and Totals
                            Michael Mixon

                            Got it...yeah, I see how the logic behaves differently with numeric values.  Thanks for the clarification. 

                             

                            I also played around with my own data and do see the issue when there is only a single row within any segment, but this is still a very helpful solution for the majority of the cases we deal with.  Were Tableau to come out with an ISTOTAL() function, that would of course be stellar.

                            • 11. Re: Different Table Calc Context for SubTotals and Totals
                              Richard Leeke

                              I almost mentioned Joe's point about differentiating sub-total from grand total when I was posting that last night.  Maybe what is really needed is a TOTALDEPTH() function which returns 0 for detail rows, 1 for the first level of subtotals, 2 for the next level, etc.

                              • 12. Re: Different Table Calc Context for SubTotals and Totals
                                Joe Mako

                                Richard,

                                 

                                I am not sure that would be the complete solution. Because turning on and off subtotals is at the pill level, but that calculation you propose would depend on the layout, and could break calculations if the layout changes (hierarchy, moving pills to different shelves, etc.). I do not know what the best answer would be, maybe  another option in the Edit Table Calculation dialog for using a different partitioning setup that is based on what the Sub/Grand Total is being performed on (I am not sure if that makes any sense), but that may not work because some people want a sum at one level and an average at another. How do you determine what level of detail you are calculating at within a calculated field's formula? Your proposed calculation could give a number like an index, but do you specify what that order is? Would it be a hybrid of Level of Detail shelf and layout of pills on the other shelfs? It is a tough question, lots of potential situations and complexities (or maybe I am looking at the problem from the wrong viewpoint, making it more complex than it needs to be).

                                • 13. Re: Different Table Calc Context for SubTotals and Totals
                                  Richard Leeke

                                  Joe

                                   

                                  You are quite right - just knowing the depth still depends on assumptions about the layout and which subtotals are enabled, so that wouldn't be enough.

                                   

                                  This gets quite complex quite quickly, doesn't it?  From experimenting with the workbook you posted for Mike it's clear that the subtotal calculations operate within their own "automagically created" partitions (drop an instance of INDEX() onto the view and that is very obvious).

                                   

                                  It feels to me as if there would need to be some way of exposing what is happening with these "automagically generated" partitions in order to take control of subtotal calculations properly.  As with everything table calc related, it's actually a hard problem - so one of the questions has to be whether the problem we are trying to solve is significant enough to justify the extra complexity that the solution would add.

                                  • 14. Re: Different Table Calc Context for SubTotals and Totals
                                    Joe Mako

                                    Yes, I agree, it may add complexity with limited benefit.

                                     

                                    A possible solution that I was thinking of is to pad the data with an additional row to each possible level of detail, and use a table calculation filter to hide those rows.

                                     

                                    If you can provide a data set set that the above formula does not work for, I think I can provide a working solution, or maybe some custom SQL that can be adapted to work in other situations.

                                    1 2 Previous Next