Reply
Regular Visitor
Posts: 24
Registered: ‎06-15-2017

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!!!

Super User
Posts: 10,429
Registered: ‎07-11-2015

Re: Ranking Issues with Measures

[ Edited ]

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)

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

Proud to be a Datanaut!


Regular Visitor
Posts: 24
Registered: ‎06-15-2017

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
Posts: 10,429
Registered: ‎07-11-2015

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

 

 


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

Proud to be a Datanaut!


Regular Visitor
Posts: 24
Registered: ‎06-15-2017

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!

Regular Visitor
Posts: 24
Registered: ‎06-15-2017

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
Posts: 3,918
Registered: ‎01-14-2017

Re: RankX Measure Issue

Hi,

 

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

Regular Visitor
Posts: 24
Registered: ‎06-15-2017

Re: RankX Measure Issue

Hello,

 

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

 

Thanks,

 

Garrett Gilmore

Super User
Posts: 3,918
Registered: ‎01-14-2017

Re: RankX Measure Issue

Hi,

 

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

Regular Visitor
Posts: 24
Registered: ‎06-15-2017

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