Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ViorelCa
Helper I
Helper I

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
bsas
Post Patron
Post Patron

Anonymous
Not applicable

Give it a try...

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

View solution in original post

Anonymous
Not applicable

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

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Give it a try...

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

Thanks a lot, @Anonymous,

 

It works like a charm!

 

Kind regards,

Viorel

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
Not applicable

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

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

Again it works like a charm!

 

I remain indebted to you, @Anonymous!

 

Have a nice day!

Viorel

bsas
Post Patron
Post Patron

Hi,

 

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

Thanks a lot, @bsas!

 

Very useful and to the point!

 

Kind regards,

Viorel

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.