I am testing pareto principle and calculating the top N% of revenue and the underlying responsible customers.
Customer are being ranked by amount of their rev value:
Revenue Rank =CALCULATE(
RANKX(ALL(RevData[enduser_cust_name]); [Total Revenue FY 18]; ;DESC);
ALLSELECTED(RevData[Portfolio])
)
Now I want to compare the numbers of all (unique) customers with the number of customers that make up N% of revenue, so I get the customers that are making up the N% of revenue:
( N% can be selected by What-If slicer 'Revenue %'[Revenue % Value] )
Top R% Customers =
IF(ISBLANK([Total Revenue FY 18]) ;
BLANK();
IF(RANKX(ALL(PRevData); [Total Revenue FY 18]; ;DESC) < (CALCULATE( [Unique Revenue Customers];ALL(RevData) ) * ('Revenue %'[Revenue % Value]/100) );
[Total Revenue FY 18];
BLANK()
)
)
its all working ok, [Top R% customer] column is showing blanks, once selected N% revenue is reached.
However I am not able to get to identify the rank, that corresponds to [Top R% customer] becoming blank, which is the number of customers responsible for N% of revenue.
Searching the forum I see posts that relate to maxx(column), however this is a measure + its only a subset of the underlying table, so countrows would not work either.
Any idea appreciated.