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

# Trying to build a concentration KPI

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

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

 Distributor Reseller Revenue A Revenue B Incentive A Incentive B Total Revenue Total Incentive AAA A 10 16 0.2 0.58 26 0.78 AAA B 15 35 0.145 1.2 50 1.345 AAA C 20 42 1.35 1.46 62 2.81 AAA D 25 12 0.05 0.61 37 0.66 AAA A 30 37 1.3 1.41 67 2.71 AAA B 10 320 0.03 9.7 330 9.73 AAA C 40 22 1.3 1.06 62 2.36 AAA B 45 21 0.7 1.08 66 1.78

And the kind of KPIs I'm looking for are

 KPI 1 KPI 2 KPI 3 KPI 4 KPI 5 KPI 6 Resellers making 80% revenue A Resellers making 80% revenue B Resellers making 80% incentive A Resellers making 80% incentive B Resellers making 80% revenue Resellers making 80% incentive 3 2 2 2 2 2

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".

• ###### Re: Trying to build a concentration KPI

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

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

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

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