7 Replies Latest reply on May 2, 2016 12:28 AM by Tableau kumar

    Table Calcs - Top 20 % products

    Rishabh Dhingra

      Hi Guys,

       

      In the attached workbook, I am trying to find out :

       

      How much of profit (% of total profit) does top 20% of products by sales contribute to total profit?

       

      I am able to find out top 20% of products by sales but I am getting stuck while calculating their profit contribution.

       

      Do I need to get the 20% of total size and then put that in top n filter and then get a view on profit? Or there is a better way to do this?

       

      Any help would be appreciated.

       

      Thanks,

      Rishabh

        • 1. Re: Table Calcs - Top 20 % products
          khalid norat

          [Profit] / {Fixed [All] : sum([Profit])}

           

          where [All] is a Calculated field with Value 'All' or any other placeholder

           

          Unfortunately I couldn't open your dashboard as I am in the office and we are still on 9.2.4
          If you need further assistance I can get back to you after work from my personal laptop

          • 2. Re: Table Calcs - Top 20 % products
            khalid norat

            I'm assuming you are using the Sample superstore data

            • 3. Re: Table Calcs - Top 20 % products
              Rishabh Dhingra

              Yes Khalid, I am using sample superstore data. I will try your solution and let you know if it helps.

               

              Thanks,

              Rishabh

              • 4. Re: Table Calcs - Top 20 % products
                Simon Runc

                hi Rishabh,

                 

                So one way to do this is to use the RANK_PERCENTILE. Although as we'll need [Product Name] in the VizLoD (so the Table Calculation can run over that product name level. This creates a little bit of complexity, but we can get round it. In the attached there is a 'How it works' tab, which shows the builld up of the formulas so you can see what each is doing, before we get to the final solution. I've also split the various formulas up here, for explanatory purposes, but you can nest them into 1 or 2 in you final solution (if you wish)

                 

                So first thing to do is get the Top 20% of products by Sales

                [Percentile Sales]

                RANK_PERCENTILE(SUM([Sales]))

                 

                Then we can grab just the Top 20 of these products and get their Profit

                [Top 20 % Profit]

                IIF([Percentile Sales]>=0.8,SUM([Profit]),0)

                 

                Noe this gives us the profit, populated for each of the Top 20% of products, but we want just one figure (the SUM of these) so we use WINDOW_SUM

                [Top 20 % Profit Window_Sum]

                WINDOW_SUM([Top 20 % Profit])

                 

                We also need the total profit for all products

                [Total Profit]

                TOTAL(SUM([Profit]))

                 

                So we now have all the measures we need...and create the %age of Profit through the Top 20% of products

                [Top 20% Sales %age of Total Profit]

                [Top 20 % Profit Window_Sum]/[Total Profit]

                 

                I've then put the product name into the detail shelf (as we need this in the VizLoD for the above calculations to work), which you can see in the 'Final Solution_Almost' tab.

                 

                However we now have the problem, of the correct result, but repeated for each product...so we can fix this with the nifty addition of an IF FIRST()=0..END

                [Top 20% Sales %age of Total Profit - Only Return 1]

                IF FIRST()=0 THEN [Top 20 % Profit Window_Sum]/[Total Profit]END

                 

                So this returns the value for the 1st product, and the rest are NULL, so don't get plotted

                 

                Then one final thing, else we get odd column widths is to turn off the stack marks

                Hope this helps and makes sense, but please post back if not (on either count!)

                • 5. Re: Table Calcs - Top 20 % products
                  khalid norat

                  Great let me know if it works.

                  • 6. Re: Table Calcs - Top 20 % products
                    Rishabh Dhingra

                    Hi Simon,

                     

                    This is great, exactly what I was looking for. I think I was missing the trick of using Rank_Percentile calculation which actually opened the doors.

                     

                    I have nested the calculation in my final solution as :

                     

                    WINDOW_SUM(IF [Rank Sales]>=0.8 then sum(Profit) else 0 end)/TOTAL(sum([Profit]))

                     

                    Thanks for your help. Much appreciated.

                     

                    Regards,

                    Rishabh