Show Top N based on variation - Power BI

[ViorelCa]

Dear Colleagues,

I have a measure that is simply the variation of sales YTD.

varYTD = DIVIDE([SalesYTD 2017],[SalesYTD 2016])-1)

And a report in Power BI where on rows I have the stores and on columns I have: Sales 2016, Sales 2017 & varYTD.

How can I show only the top 10 stores based on the biggest varYTD (not based on the biggest sales)?

Many thanks,

Viorel

Bucharest

[bsas · Accepted solution]

Hi,

This tutorial can be usefull for you: https://www.youtube.com/watch?v=S7eMccO1_bE

[Anonymous · Accepted solution]

Give it a try...

IF(RANKX(ALLSELECTED(STORE[STORES]),varYTD,,0,DENSE)<=10,varYTD,BLANK())

[Anonymous · Accepted solution]

Give it a try...

IF(RANKX(ALLSELECTED(STORE[STORES]),varYTD,,0,DENSE)<=10,varYTD,BLANK())

[ViorelCa]

Thanks a lot, @Anonymous,

It works like a charm!

Kind regards,

Viorel

[ViorelCa]

Dear @nvpraveenyakkal,

How do I modify it for Bottom 10?

I have tried to replace "<=10" with ">TotalNumberofStores - 10", but it shows all stores.

Many thanks,

Viorel

[Anonymous · Accepted solution]

Instead of "0" try with "1" represents asceding order ... This should work...

IF(RANKX(ALLSELECTED(STORE[STORES]),varYTD,,1,DENSE)<=10,varYTD,BLANK())

[ViorelCa]

Again it works like a charm!

I remain indebted to you, @Anonymous!

Have a nice day!

Viorel

[bsas · Accepted solution]

Hi,

This tutorial can be usefull for you: https://www.youtube.com/watch?v=S7eMccO1_bE

[ViorelCa]

Thanks a lot, @bsas!

Very useful and to the point!

Kind regards,

Viorel