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.




      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


            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


            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;



                    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



                      • 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,' - ',',')