3 Replies Latest reply on Dec 10, 2012 11:21 AM by Michael Mixon

    Comparing to a Grand Total

    Michael Mixon

      Hi,

       

      I put together a viz for work today that required me to a) show the % of total by region, and b) compare that % of total to the WW % of total.  I was able to do that via a few table calculations, but I wonder if there might be a more efficient way to achieve the same end result.

       

      I've attached a remake of the chart using the Superstore Sales data.

       

      -Mike

        • 1. Re: Comparing to a Grand Total
          Tracy Rodgers

          Hi Michael,

           

          This is probably the same approach that I would take. Maybe Joe Mako has some better ideas?

           

          -Tracy

          • 2. Re: Comparing to a Grand Total
            Joe Mako

            It looks like you can to compute the sum of Sales at four different level of detail, and then perform calculations on those resulting numbers.

             

             

            - Region-Quarter-Year (RQY)

            - Region-Year (RY)

            - Quarter-Year (QY)

            - Year (Y)

             

             

            Generating:

            Linearity %  = RQY/RY

            Linearity % (US) = QY/Y

            Delta = (RQY/RY)-(QY/Y)

             

             

            You have a great deal of options in routes that you can use to get these results. So I am going going to pick one to detail here, let me know if you are interested in others.

             

             

            I like what Tom Brown says: "The Advanced Compute Using dialog should be called the Easy Compute Using dialog." I tend to avoid the advanced dialog because it is so many clicks, but it does make the concept of partitioning and addressing easier to follow and configure. So lets use the use the Advanced dialog to configure this.

             

             

            Our deepest level of detail needed is the combination of Region-Quarter-Year, so will will need a dimension (non-aggregated) pill for each of those fields active on the shelves.

             

             

            Then three calculated fields all with the same formula:

             

             

            TOTAL(SUM([Sales]))

             

             

            with three different names: Region-Year Sales, Quarter-Year Sales, Year Sales

             

             

            The name of each of these included the dimensions we will want to use for partitioning, and the other dimensions not in the field name will be used for addressing (compute using).

             

             

            Notice I did not recommend creating a field for Region-Quarter-Year, that is because a pill or a reference to SUM([Sales]) is that when all three of those dimension pills are active on the shelves.

             

             

            Then we can create the three calculated fields that we will use in the view:

             

             

            Linearity %:

            SUM([Sales])/[Region-Year Sales]

             

             

            Linearity % (US):

            [Quarter-Year Sales]/[Year Sales]

             

             

            Delta:

            [Linearity %] - [Linearity % (US)]

             

             

            Then we can build the worksheet.

             

             

            1. place three dimension pills on the Rows shelf:

            Region

            YEAR([Ship Date])

            QUARTER([Ship Date])

            2. place a pill for SUM(Sales) on the text shelf (nice to see the value for double checking)

            3. place one of first three calc fields made (a TOTAL(SUM([Sales])) field) the calculated fields directly on top of the values displayed, so Tableau generates the Measure Names/Values pills

            4. place the other two TOTAL(SUM([Sales])) fields on the Measure Values shelf

            5. configure the three table calc pills based on their name

            - Region-Year Sales: Compute using on Quarter of Ship Date (from Advanced dialog)

            - Quarter-Year Sales: Compute using on Region

            - Year Sales: Compute using on Quarter of Ship Date and Region (from Advanced dialog)

            6. place other pills

            7. format as desired.

             

             

            By following these steps, you take advantage of how Tableau auto configures nested table calcs, making it easy to create your view if you know exactly what you want.

             

             

            There are quite a few other details, but this is the general approach I would use in this situation.

            1 of 1 people found this helpful
            • 3. Re: Comparing to a Grand Total
              Michael Mixon

              Thanks Joe.  Your approach is very similar to mine, just a bit more elegant.  The main differences I noticed were:

               

              • You used total(sum([Sales])) for all three, whereas I used window_sum(sum([Sales])) for one of the calculations.  We got the same results, but I suspect being consistent is a better approach.  Does TOTAL provide any other benefits (i.e. performance) over WINDOW_SUM?
              • You used the measure [Region-Year Sales] in your Linearity % calc, whereas I used the Tableau-generated calculation TOTAL(SUM([Sales])).  Again, we got the same results, but having the explicitly named measure in the calculation may make it easier to troubleshoot and/or set contexts.

               

              -Mike