RANKX very slow performance

Question

Dear community,&nbsp;I want to carry out a Pareto analysis segmenting Suppliers in "A", "B" and "C" categories, based on a ranking of (cumulative) Spend per supplier:&nbsp;A = largest suppliers, accounting for the first 80% of SpendB = middle group of suppliers, accounting for next 15% of SpendC = smallest suppliers, accounting for final 5% of Spend&nbsp;To that end, I have used the below formula to assign the A, B or C to the suppliers, making use of RANKX:&nbsp;ParetoAnalysisSegmentation = VAR TotalSpend = &nbsp; &nbsp;CALCULATE( [D_Spend_MaxYear] , ALL( DIM_Suppliers[Supplier name] ) )VAR Ranking = &nbsp; &nbsp; &nbsp; RANKX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL( DIM_Suppliers[Supplier name]&nbsp;) , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DIVIDE( [D_Spend_MaxYear] , TotalSpend ) ) VAR&nbsp;Segmentation&nbsp;= &nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DIVIDE( [D_Spend_MaxYear] , TotalSpend ) , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER( ALL(&nbsp; DIM_Suppliers[Supplier name]&nbsp;) &nbsp;, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RANKX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL(&nbsp; DIM_Suppliers[Supplier name]&nbsp;) , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DIVIDE( [D_Spend_MaxYear] , TotalSpend ) )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;= &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Ranking&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )RETURNIF( [D_Spend_MaxYear] = BLANK() , &nbsp; &nbsp; BLANK() , &nbsp; &nbsp; &nbsp; &nbsp; SWITCH( TRUE() , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Segmentation&nbsp;&lt;= 0.8 , "A" , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Segmentation&nbsp;&lt;= 0.95 , "B" , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "C"&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;Whereby measure [D_Spend_MaxYear] calculates Spend.&nbsp;This formula works, however it is very, very slow (210 seconds according to Performance Analyzer). Any guidance on how to improve the performance of this formula? I think it has something to do with RANKX, but am at a loss on how to proceed.&nbsp;&nbsp;Any help is appreciated!

v-luwang-msft · Answer

Hi&nbsp; &nbsp;MisterWLF&nbsp;，
Pls share your pbix file ,remember to remove confidential data.
&nbsp;
&nbsp;
Best Regards
Lucien

misterwlf · Answer

Hi Lucien, anonymized pbix file is&nbsp;here&nbsp;.&nbsp;Please use the column / measure names as included in the file. The expected outcome is already there, but as you can see the DAX is very slow. Any help on how to speed this up to a workable format is appreciated!

Forum Discussion

RANKX very slow performance

2 Replies

Related content

RankX

DAX Performance

DAX performance optimization

Fabric Pipeline Performance

RANKX