Top N% of Spend - Measure calculating forever

[mohammedismail]

Hello Everyone 

I have been trying to Identify N% of Total Spend amount.. I want to see the list of suppliers who contrinute to N% of the total spend.

Top / Tail Filter = 
var currentRank = RANKX(ALLSELECTED(Master_Supplier_Lookup[Supplier - ERP Supplier]),Invoice_Spend[Invoice_Spend_M])

var Running_Total =
CALCULATE(
        Invoice_Spend[Invoice_Spend_M],
            FILTER(ALL(Master_Supplier_Lookup[Supplier - ERP Supplier]),
                currentRank >= RANKX(ALLSELECTED(Master_Supplier_Lookup[Supplier - ERP Supplier]),
                Invoice_Spend[Invoice_Spend_M])))

Return IF(Invoice_Spend[Invoice_Spend_M] <= Running_Total*0.8,1,BLANK())

I'm using the above code to get the running total and then Identify the Suppliers which fall below the N% (i have hard coded the value 80% in this code). But this measure takes forever to return the results.. 

Can someone please help me in fixing this code or let me know an alternate way to achieve this.

TIA 

[Bubble4502]

Hi @mohammedismail ,

Based on your description, I have created a simple sample:

Please try:

First, create a new table for slicer:

Then create a measure:

Top / Tail Filter = 
var _a = ADDCOLUMNS(ALL('Table'),"r",RANKX(ALL('Table'),[Value],,ASC))
var _b = MAXX(FILTER( _a,[Suppliers]=MAX('Table'[Suppliers])),[r])
var _runningtotal = CALCULATE(SUM('Table'[Value]),FILTER(_a,[r]<=_b))
Return IF(MAX('Table'[Value])<_runningtotal*SELECTEDVALUE('For slicer'[Value]),1,BLANK())

Final output:

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohammedismail]

Hey thanks for your response... But this is taking a lot of time to return results. More than 5 mins when I change the percentage value.

[mohammedismail]

can someone help on this query please.

[mohammedismail]

Bumping the thread to see if someone can help !! @amitchandak