cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stsau Frequent Visitor
Frequent Visitor

identify rank number for topN% of a measure

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()
)
 
)pbi.PNG
 
 
 
 
 
 
 


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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors