4 Replies Latest reply on Mar 31, 2017 9:39 AM by Jim Dehner

    Sum of quantity based on profit baskets (LOD & Aggregation)

    Benjamin Moerman

      Hi,

       

      Long time without being stuck. Here is my issue.

      I would like to create a vizualisation allowing me to see how volumes are split according to Profit baskets

       

      See an example below with an ugly excel printscreen of fake date showing that in February, most volumes came from the high profit baskets.

      I would like to create something similar, a bit more fancy as it's Tableau, but still showing in which profit basket is the volume coming from.

       

      I used the Sample Superstore database attached to create my baskets and unit profit.

      Now, I would like to sum my quantity according to my baskets.

       

      I spent hours with plenty of conditions and LOD (include, Fixed..). My instinct told me that I had to calculate a unit margin at customer-product (and maybe month or order date), then create some baskets like below and some them at some point...

       

      Here is a simple viz of my data. Level of aggregation is the issue here.

      Hope you can help, it could make plenty of users very happy (and hopfeully prevent hours spent on Excel reporting).

       

      thank you.

      Ben

        • 1. Re: Sum of quantity based on profit baskets (LOD & Aggregation)
          Kaz Shakir

          Benjamin,

          I'm not sure if this is what you are after, but take a look and let us know.

           

          First, I changed your unit price calculation to no longer use aggregations - you shouldn't need that there:

           

          This allowed the your Basket calculated field to now be changed from a measure to a dimension - and so I did that.

           

          Changing those two fileds broke the viz a little bit, and I had to re-place the [unit profit] field on the Measure Values shelf, this time with a SUM aggregation, and had to re-place the [Basket] field on the Rows shelf with no aggregation (since it's now a measure).

           

          Finally, I created a calculated field to total up the quantity for the viz, leaving out the customer name and product name:

           

          Hope that's helpful.  Please let us know if this addresses your question, or raises more.

          Kaz.

          • 2. Re: Sum of quantity based on profit baskets (LOD & Aggregation)
            Jim Dehner

            Hi Kaz

             

            I am not on the same version of Tableau (10.1) but lets see if this makes sense for you

             

            The result looks like this

             

             

            The calculations that I used are at the Customer  cust unit profit > { FIXED [Customer Name]: Sum([Profit])/sum([Quantity]) } - this could be customer or customer category or any other combination  -

            Then new buckets .if [cust unit profit]<0 then "below 0" ELSEIF [cust unit profit]<10 then "0-10" else "above 10" END puts the customers into buckets - this becomes a dimension

             

            The viz is just a plot of Order date (months) on the columns and the New Buckets on the Rows (I added Years on the rows shelf also to let you look across years

            Sum[Quantity] goes on the detail shelf - for an additional level of information I put profit ratio on color

             

            In the filters there are category and months that can be used in some detail to look at different levels along product

             

             

            Let me know if this helped

             

            Jim

            • 3. Re: Sum of quantity based on profit baskets (LOD & Aggregation)
              Benjamin Moerman

              Hi,

               

              Thanks both for your help!! Jim was a bit more closer to the answer I needed but thanks both

               

              I applied it to real life situation and it works fantastically!!

              I will work a bit on the design, but we can see month after month how the volume evolves, an in which profit basket it is!

               

              THANKS!

               

               

              Ben

              • 4. Re: Sum of quantity based on profit baskets (LOD & Aggregation)
                Jim Dehner

                Glad it worked for you

                This was a really interesting problem

                Glad to help out

                Jim