cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ViorelCa Regular Visitor
Regular Visitor

Show Top N based on variation - Power BI

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
bsas Member
Member

Re: Show Top N based on variation - Power BI

Hi,

 

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

Re: Show Top N based on variation - Power BI

Give it a try...

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

Re: Show Top N based on variation - Power BI

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

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

7 REPLIES 7
bsas Member
Member

Re: Show Top N based on variation - Power BI

Hi,

 

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

Re: Show Top N based on variation - Power BI

Give it a try...

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

ViorelCa Regular Visitor
Regular Visitor

Re: Show Top N based on variation - Power BI

Thanks a lot, @nvpraveenyakkal,

 

It works like a charm!

 

Kind regards,

Viorel

ViorelCa Regular Visitor
Regular Visitor

Re: Show Top N based on variation - Power BI

Thanks a lot, @bsas!

 

Very useful and to the point!

 

Kind regards,

Viorel

ViorelCa Regular Visitor
Regular Visitor

Re: Show Top N based on variation - Power BI

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

Re: Show Top N based on variation - Power BI

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

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

ViorelCa Regular Visitor
Regular Visitor

Re: Show Top N based on variation - Power BI

Again it works like a charm!

 

I remain indebted to you, @nvpraveenyakkal!

 

Have a nice day!

Viorel