5 Replies Latest reply on Dec 20, 2016 6:34 AM by Eric Sturgeon

    Fixed Category Sales Level of Detail: Not the same value for each Sub-Category

    Eric Sturgeon

      I'm having an issue with fixed level of detail not working as I thought it would ...  I'll use the Superstore data to explain.

       

      I created a calculated field to sum the category sales so that I can see each sub-category's share of the category it belongs to.  I want to be able to drill down geographically, so I also included Postal Code in the level of detail.

       

      Here's my calculated field called "Total Category Sales": { FIXED [Category], [Postal Code] : sum([Sales]) }

       

      To calculate share of category, I would create another calculated field and enter: sum([Sales]/sum([Total Category Sales].

       

      Unfortunately, as you can see in the screen shot below, total category sales are not being computed correctly for each sub-category.  They should all be the same (742,000 in the case of Furniture) but they are different values for each sub-category.   I didn't expect this to happen as I thought that using level of detail would exclude other dimensions in the view, in this case sub-category, but it seems that category volume is not being summed in any postal code that did not have sales of the sub-category.  I know that its [Postal Code] in the level of detail that's screwing this up because if I take [Postal Code] out of the calculated field, total category sales are correct (the same for each sub-category within each category).  However, I need to be able to drill down geographically and if I remove [Postal Code], I would get industry volume for the entire country, not the region I've drilled down to.  Any thoughts on what I can do?

       

      Thanks,

      Eric

       

       

       

      Capture.JPG

        • 1. Re: Fixed Category Sales Level of Detail: Not the same value for each Sub-Category
          Matthew Risley

          Super quick- Haven't opened your workbook so please excuse this if this is an insultingly bad answer:

           

          I don't see postal code in your view or in the filters.

           

          If you added that would it solve this issue?

          • 2. Re: Fixed Category Sales Level of Detail: Not the same value for each Sub-Category
            Eric Sturgeon

            I didn't include postal code in this view because sometimes I'll be looking at sales as an aggregate but I need to be able to drill down as far as the postal code level and for the total category sales to be filtered accordingly.

            • 3. Re: Fixed Category Sales Level of Detail: Not the same value for each Sub-Category
              Khang Pho

              Hi Eric,

               

              I believe it is not exactly the same since each Category broken down by Subcategory might have a different number of zip codes to it.  Even though its not being shown Furniture-Bookcases might have 3 zip codes while Furniture-Chairs might have 6 Postal Codes.  If that is the case it might be adding the Sales for the Postal Codes in those sub-categories.

               

              Have you tried using exclude instead?  {EXCLUDE [Sub-Category]:Sum(Sales)}

              • 4. Re: Fixed Category Sales Level of Detail: Not the same value for each Sub-Category
                Jonathan Drummey

                Hi Eric,

                 

                What's happening is that you're getting replication of values at a record level due to how LODs work, Khang Pho was close, the replication is actually due to the Sub-Category dimension in the view.

                 

                The FIXED LOD is computed at the given level of detail - Category & Postal Code in this case - and then joined back to the viz level of detail (vizLOD) - Category & Sub-Category in this case. So the Category & Postal Code values are getting replicated for each Sub-Category in each Category, then summed up. The  with postal code view demonstrates this replication:

                 

                Screen Shot 2016-12-17 at 12.44.31 AM.png

                 

                The reason why the FIXED LOD matches the SUM(Sales) in the Subtotal is the same in your original sheet is that Subtotals are computed at a different level of detail, in this case the subtotal is at the level of detail of Category so the FIXED LOD that is returning the per Category & Postal Code values is getting summed at the Category level and there's no replication.

                 

                I'm not clear on exactly the path that you're looking to drill down through, if you could provide more details then we can figure something out that may or may not use LOD expressions. I did a quick example with a hierarchy with Category/Sub-Category/Postal Code (in that order) in the cat/subcat/postal code worksheet. A Percent of Total Quick Table Calculation with a Compute Using of Pane (down) recomputes the total based on the right-most dimension, so as we expand & collapse the hierarchy the % of total changes:

                 

                Screen Shot 2016-12-17 at 12.51.09 AM.png

                 

                Screen Shot 2016-12-17 at 12.51.29 AM.png

                 

                Also, I noticed you are on v9.0. If you are going to use LOD expressions and can upgrade your Tableau version at all, please do so. Besides a number of performance optimizations and more flexibility in building LOD expressions there have been a few bug fixes where under certain (complicated view) circumstances LOD expressions would return incorrect results.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Fixed Category Sales Level of Detail: Not the same value for each Sub-Category
                  Eric Sturgeon

                  Jonathan, thank you for your help.  The TOTAL formula mostly worked for my purposes, with the only limitation being that if I filter to a particular sub-category, total category sales will no longer be accurate.  My company hasn't upgraded to the latest Tableau version but when they do hopefully I will have more options with LOD expressions.