How to optimize the RANKX measure

[a4]

Hi all

I have a  rank measure:

Rank  = CALCULATE( RANKX(ALL('salesorderdetails'[Trim name]),ROUND([Gross Premium(USD) minus Commission (USD)],2),,DESC,Dense),ALLSELECTED('salesorderdetails'),VALUES('salesorderdetails'[Trim name]))

When I remove this measure from table visual it brings down the excution time down to 2 seconds and performance is improved. So, clearly there is a problem with the Rank measure.

Please suggest a way to optimize the RANKX function.

Kind Regards

Amit Kumar

[amitchandak]

@a4 , Try like

Rank = RANKX(ALLSELECTED('salesorderdetails'[Trim name]),ROUND([Gross Premium(USD) minus Commission (USD)],2),,DESC,Dense)

[a4]

Hi Amit,

This formula doesn't give the desired result. All the ranks are same.

Kind Regards

Amit Kumar

[az38]

Hi @a4 

first, try to debug it.

remove ALLSELECTED('salesorderdetails') and check performance

then remove VALUES('salesorderdetails'[Trim name]) and check performance

in order to find the biggest memory consumer.

then we can try to suggest smth.

[harshnathani]

Hi @a4 ,

Try this.

Rank =

RANKX(ALL('salesorderdetails'[Trim name]),ROUND([Gross Premium(USD) minus Commission (USD)],2),,DESC,Dense)

Also, if you can share sample data. 

Regards,

Harsh Nathani

[harshnathani]

HI @a4 ,

Share sample .pbix and screen shot of your table and vizualisation.

Also you can try this

RANKX(ALLSELECTED('salesorderdetails'[Trim name]),CALCULATE(ROUND([Gross Premium(USD) minus Commission (USD)],2)),,DESC,Dense)

Regards,

Harsh Nathani

[a4]

Hi Harsh,
This formula also didn't work.Please find a acreenshot for better understanding

[Gross Premium(USD) minus Commission (USD)] (Which is used in rank measure) = Net Premium (USD)

Please suggest a better formula for optimization.

Kind Regards

Amit Kumar

[harshnathani]

Hi @a4 ,

What was the output of the above formula.

Not sure why this is not working. Maybe something to do with measures.

RANK = RANKX(ALL(Salesorderdetails),ROUND([Gross Premium(USD) minus Commission (USD)],2)),,DESC,Dense)

Regards,

Harsh Nathani

[a4]

Hi Harsh,

The formula which I have used gives the correct result, the only problem is that it is not optimized.

Please refer my first post for the formula.

Kind Regards

Amit Kumar

[a4]

Hi Harsh,

The formula which you mentioned doesn't give the desired result.All the Trim Name has rank with this formula.

Please suggest anything else.

Kind Regards

Amit Kumar

[a4]

Hi Harsh,

The formula which you mentioned doesn't give the desired result.All the Trim Name has the same rank with this formula.

Please suggest anything else.

Kind Regards

Amit Kumar