Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have used below to calculate the accumulating %
RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $])
How would I build off of this to count how many customers make up 50%
@MPH you can try with full descending word
=count(aggr(if(RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $]) <=0.8,CustomerID),(CustomerID,(= Sum([OM $]), DESCENDING))))
@MPH On what dimensions this cumulative % need to be calculated? Can you share sample with expected output?
Number of unique customers
=count(distinct([Cust Nbr])) this gives me the total unique customers I want to be able to count how many make up say 80% of margin ( Count of Green Highlighted)
@MPH what are the dimension of table?
Customer ID
@MPH try below. sorting is very important here. assuming you have sorted your table with sum([OM $])
=count(aggr(if(RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $]) <=0.8,CustomerID),(CustomerID,(= Sum([OM $]), Desc))))
What is the Desc ?
@MPH It is sort "Descending" on expression " Sum([OM $])" by customer ID
@MPH you can try with full descending word
=count(aggr(if(RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $]) <=0.8,CustomerID),(CustomerID,(= Sum([OM $]), DESCENDING))))