Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am facing issue with one of the KPI. I need to calculate Yearly accumulated of Spend_actual per supplier . Sample data as below , Here I need to get count of total suppliers who provided 80% of spend (KPI1) and also need to count the % of the supplier who provided 80% of Spend (KPI2) .
#suppliers providing 80% spend = count of (supplier in top 80 % spend )
% suppliers providing 80% spend = count of (supplier in top 80 % spend ) /Total suppliers
I came up with formula to calculate accumulated spend_actual per supplier :
num((RangeSum(Above(SUM({<$(vSet)>}SPEND_ACTUAL), 0, RowNo())))/(sum({<$(vSet)>}SPEND_ACTUAL)),'###.##%)
But I am struggling to get top suppliers who provided 80% spend.
Kindly Please help.
Parent Supplier | Sum(SPEND_ACTUAL)/1000000 | Sum(SPEND_ACTUAL)/Sum(total SPEND_ACTUAL) | RangeSum(Above(Sum(SPEND_ACTUAL),0,RowNo()))/sum(total SPEND_ACTUAL) //0 offset | Supplier in top 80% spend? Formula - =IF(D<=80%,1,0) |
A | 118.51 | 3.5% | 3.5% | 1 |
B | 94.96 | 2.8% | 6.3% | 1 |
C | 79.01 | 2.3% | 8.6% | 1 |
D | 74.90 | 2.2% | 10.9% | 1 |
E | 71.93 | 2.1% | 13.0% | 1 |
F | 67.42 | 2.0% | 15.0% | 1 |
G | 61.97 | 1.8% | 16.8% | 1 |
H | 47.12 | 1.4% | 18.2% | 1 |
I | 43.00 | 1.3% | 19.5% | 1 |
J | 36.22 | 1.1% | 20.6% | 1 |
K | 27.60 | 0.8% | 21.4% | 1 |
L | 26.43 | 0.8% | 22.2% | 1 |
M | 25.72 | 0.8% | 22.9% | 1 |
After few try, I came up with below formula but both are giving error saying "Error in Expression : ')' expected"
% suppliers providing 80% spend =
num(if((RangeSum(Above(SUM({<$(vSet),GP_IN_COUNTRY = {'Y'}>}SPEND_ACTUAL),0,RowNo()))/sum({<$(vSet),GP_IN_COUNTRY = {'Y'}>}SPEND_ACTUAL))<= {'0.80'},
RangeSum(Above(Count(distinct {<$(vSet), GP_IN_COUNTRY = {'Y'}>}PARENT_SUPPLIER_ID), 0,RowNo())))
/Count(distinct {<$(vSet), GP_IN_COUNTRY = {'Y'}>}PARENT_SUPPLIER_ID)
,'$(vPerFormat)')
#suppliers providing 80% spend =
num(if(
(RangeSum(Above(SUM({<$(vSet), GP_IN_COUNTRY = {'Y'}>}SPEND_ACTUAL),0,RowNo()))
/
sum({<$(vSet),GP_IN_COUNTRY = {'Y'}>}SPEND_ACTUAL))<= {'0.80'},RangeSum(Above(Count(distinct {<$(vSet), GP_IN_COUNTRY = {'Y'}>}PARENT_SUPPLIER_ID), 0,RowNo())))
,'$(vNumFormat)')
Please help me to fix this.