Trying to build a concentration KPI

[rebostero]

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.

Seller	Amount
1	100K
2	50K
3	400K
4	400K
5	50K

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

[william_fu]

See Recipe for a Pareto Analysis – Revisited

[zebhashmi]

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

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

[rebostero]

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

Thanks in advance

[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))

    )

)

[rebostero]

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!

[rebostero]

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