Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
yap one you know your seller you can put them in concat kpi
=Concat({1<your formulla>}Seller,' - ',',')
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
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))
)
)
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!
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