Rangesum with If condition

[ArchanaB]

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

[ArchanaB]

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.