6 Replies Latest reply: Sep 11, 2018 8:59 AM by Sebastian Spur RSS

    Trying to build a concentration KPI

    Sebastian Spur

      Hi and thanks in advance

       

      For arguments sake - I have a database of transactions with four fields:

       

      1) City

      2) Client

      3) Seller

      4) Amount

       

      I want to get a KPI that shows for example, how many Sellers represent 80% of total sales for a Client or City

       

      Example - Total Sales for city A is 1M.

       

      SellerAmount
      1100K
      250K
      3400K
      4400K
      550K

       

      In this case - Sellers 3 and 4 concentrate 80% of all sales - so 2 out of 5 Sellers are generating 80% of the revenue in this client. As I have this transactionally, I first need to find the total amount for each seller for the specific client or city, then calculate how many from the total sellers are concentrating 80% of the revenue. Any help will be appreciated, thanks

          • Re: Trying to build a concentration KPI
            Sebastian Spur

            Thanks for the help, I may be thick but I am not being able to adapt the formulas to my structure. This is the kind of structure I have built from multiples sources). This table shows a set of transactions

             

                 

            DistributorResellerRevenue ARevenue BIncentive AIncentive BTotal RevenueTotal Incentive
            AAAA10160.20.58260.78
            AAAB15350.1451.2501.345
            AAAC20421.351.46622.81
            AAAD25120.050.61370.66
            AAAA30371.31.41672.71
            AAAB103200.039.73309.73
            AAAC40221.31.06622.36
            AAAB45210.71.08661.78

             

            And the kind of KPIs I'm looking for are

             

             

            KPI 1KPI 2KPI 3KPI 4KPI 5KPI 6
            Resellers making 80% revenue AResellers making 80% revenue BResellers making 80% incentive AResellers making 80% incentive BResellers making 80% revenueResellers making 80% incentive
            322222

             

            I don't need to identify which resellers are making the 80%, but I do need to know how many resellers. This way, I can say "5% of all my resellers are generating 80% of all my revenue".

             

            Thanks in advance

              • Re: Trying to build a concentration KPI
                William Fu

                Check the first comment in the post I linked you;


                Count(Distinct

                Aggr(


                    If(Rangesum(Above(Sum({1} [Revenue A] )/Sum({1} total [Revenue A]),1,RowNo()))<0.8, Reseller),


                    (Reseller,(=Sum({1} [Revenue A]),desc))


                    )

                )

                  • Re: Trying to build a concentration KPI
                    Sebastian Spur

                    Thanks for your help... I'm getting there but still seeing some issues.

                     

                    count(distinct aggr(if(rangesum(Above(Sum({1} [Total] )/Sum({$} total [Total]),1,RowNo()))<0.8,Company),(Company,(=Sum({1} Total),Desc))))

                     

                    When I use the $ sign, I get the right numbers in the Sums (as I need this to be variable depending on the filters) but the aggr does not work.

                     

                    When I use the 1 instead of the $, I don't get the right numbers with aggr, slightly off but still off -  in the attached picture (Capture), you can see what I mean - correct number should be 3 in the KPI. In the second attachment (Capture2), you can see the numbers

                     

                    Thanks!

                      • Re: Trying to build a concentration KPI
                        Sebastian Spur

                        So I finally worked it out...

                         

                        count(distinct aggr(if(rangesum(Above(Sum({1} [Total] )/Sum({$} total [Total]),1,RowNo()))<0.8,Company),(Company,(=Sum( Total),Desc))))

                         

                        This allowed me to have the KPI recalculated for each Distributor (which would be 1 level above Company = Reseller) when the distributor filter changed

                         

                        Thanks for the help

                • Re: Trying to build a concentration KPI
                  Jahanzeb Hashmi

                  yap one you know your seller you can put them in concat kpi

                   

                  =Concat({1<your formulla>}Seller,' - ',',')