2 Replies Latest reply on Jun 26, 2017 12:15 PM by Michael Gillespie

    LOD calculation for % of sub category

    Alex Martino

      I don't understand how this formula grabs the sum of all the subcategory? Currently, I have product category and sub category on the rows.

       

      The first part sums each sub category, right? How does the second ONLY sum the entire subcategory?

       

      Sum(Sales)

      /

      SUM({EXCLUDE [Product Category]: Sum(Sales)})

        • 1. Re: LOD calculation for % of sub category
          Prayson Wilfred Daniel

          This has to do with the level of detail in your visualisation

           

          If you partition your SUM([Sales]) with Product Category in your visualisation, you can exclude that partition by EXCLUDE LOD.

           

          So Sum(Sales)/SUM(Exclude [Category]:SUM(Sales)}) is same as finding the percentage of each category sale ( category/All categories).

           

          60491a2c-6281-449c-91a7-d189b77d243e.gif

          • 2. Re: LOD calculation for % of sub category
            Michael Gillespie

            The Level of Detail calcs tell Tableau to perform a calculation in a specific way, regardless of anything else that might be going on in the view.  So, your calculation breaks down as follows:

             

            {EXCLUDE [Product Category]: - Even if Product Category is in the view, do not use it in the calculation that follows this colon:

            SUM([Sales])} - Sum the value of Sales for every row in the view at whatever level of detail is in the view (but apply the rule from the previous "EXCLUDE" statement).  You're removing the higher level portion of the hierarchy from the calculation.

             

            So, if what you want is to get the rolled-up value of all the values in Sub-Category, that formula is not going to be what you want.

             

            Your FIRST SUM([Sales]) says "Add up the Sales values!"  If your view is at the Sub-Category level, then you'll get a SUM by Sub-Category. If you roll up to Product Category, then your SUM will be at that level.  That's your numerator.  In this case, though your denominator is going to equal your numerator so your result will be '1'.  Is that what you want?

             

            Are you trying to represent the value of each Sub-Category's sales as a percentage of Total Sales for that Product Category?