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

Ranking Issues with Measures

 

I would like to create a Top 5 and Bottom 5 ranking based off of a Measure that is interactive based off of the filter selections.

 

A picture is posted below(Dummy Data).. That I will further explain

 

Power Bi Ranking.PNG

 

So in the table I have a Market Column which is in my "Geography" Table and I have a category column which is in my "Retailer Table"... POS USD TY and POS USD LY are both measures (Foreign Currency/USD Conversion Rate) ... IYA is another measure that is taking POS USD TY / POS USD LY *100... I would like to have another column that ranks The Top 5 IYA's and Bottom 5 IYA's...

 

As I choose my time filter -- Last Week ,Last 4 Weeks, Last 52 Weeks I want the table and ranking systems to change based off selections. Currenlty I'm stuck and don't know what formula to use..  TOPN as a visual filter does not work .. I know I need a Rankx formula.. Please help!!!

18 REPLIES 18
Super User
Super User

Re: Ranking Issues with Measures

Perhaps something along the lines of:

 

MyRankTop = RANKX(ALLSELECTED('Table'),[IYA],[IYA],DESC)

MyRankBottom = RANKX(ALLSELECTED('Table'),[IYA],[IYA],ASC)

IsTop5 = IF([MyRankTop]<=5,1,0)

IsBottom5 = IF([MyRankBottom]<=2,1,0)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ggilmoreatu Regular Visitor
Regular Visitor

Re: Ranking Issues with Measures

@Greg_Deckler Thank you for the reply... Unfortunately this didn't work... It produced multiple lines with the same ranking... I'm looking to have the highest IYA's be ranked the top and the lowest IYA's ranked on the bottom... Any other ideas ?

Super User
Super User

Re: Ranking Issues with Measures

You'll need to probably play with the "ties" optional parameter of RANKX. An alternative might be to fiddle around with TOPN.

 

https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax

 

https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ggilmoreatu Regular Visitor
Regular Visitor

Re: Ranking Issues with Measures

I will look into this... Thank you for the tips... Anyone else that has recomendation I would be happy to hear them!

ggilmoreatu Regular Visitor
Regular Visitor

RankX Measure Issue

I would like to create a Top 5 and Bottom 5 ranking based off of a Measure that is interactive based off of the filter selections.

 

A picture is posted below(Dummy Data).. That I will further explain

 

Power Bi Ranking.PNG

 

 

So in the table I have a Market Column which is in my "Geography" Table and I have a category column which is in my "Retailer Table"... POS USD TY and POS USD LY are both measures (Foreign Currency/USD Conversion Rate) ... IYA is another measure that is taking POS USD TY / POS USD LY *100... I would like to have another column that ranks The Top 5 IYA's and Bottom 5 IYA's...

 

As I choose my time filter -- Last Week ,Last 4 Weeks, Last 52 Weeks I want the table and ranking systems to change based off selections. Currently I'm stuck and don't know what formula to use..  TOPN as a visual filter does not work .. I know I need a Rankx formula.. Please help!!!

Super User
Super User

Re: RankX Measure Issue

Hi,

 

Share the link from where i can download your PBI file.

ggilmoreatu Regular Visitor
Regular Visitor

Re: RankX Measure Issue

Hello,

 

@Ashish_Mathur What is the best way to share the file with you ?

 

Thanks,

 

Garrett Gilmore

Super User
Super User

Re: RankX Measure Issue

Hi,

 

Upload the file to OneDrive/Google Drive and share the download link with me.

ggilmoreatu Regular Visitor
Regular Visitor

Re: RankX Measure Issue

Hello,

 

@Ashish_Mathur could you please try this (https://drive.google.com/open?id=1Yqix6czPTLJ981_TRB8VmxnvZLi07ftW)... It has the file located within the drive... I've never used Google Drive so please let me know if this doesn't work... I appreciate your help!

 

I will send you a message further breaking down the file!

 

Thanks,

 

Garrrett

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 314 members 3,625 guests
Please welcome our newest community members: