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

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

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello,

 

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

 

Thanks,

 

Garrett Gilmore

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Your question is not clear.  Please show the expected result here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

 

I would like to write a RANKX formula that ranks the TOP and BOTTOM 5 Country and Categories based off of IYA. So as different slicers are selected it will change the Top and Bottom 5.

 

For example "LA" is selected in the Region slicer then the TOP 5 will show the top 5 country/categories that have the top 5 IYA and bottom 5 IYA. As the timeframe slicer is changed as well the TOP  and Bottom 5 can change as well. Example LW Canada ^&8 could be in the top 5 but L52W it may not be. Below is a picture of how I invision is roughly looking but only 5 rows.

 

Rank.PNG

 

I know it will be a RANKX formula and I can then use the TOPN function on the filters... Thank you for all the help and I look forward to hearing from you

Hi,

 

I cannot help you because there are no figures appearing in the IYA column in your visual.  So even if i write a formula, i will not see any meaningful ranking result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

 

The way the formulas are set up is that some of the slicers need to be selected for the tables to display figures... Some categories may not have sales in certain markets so you will need to sort by IYA or POS to bring the numbers to the top of the table. Please let me know if you need anything else. I appreciate all of the help.

Hi,

 

I sorted but still did not see any numbers in the IYA column.  It was entirely blank.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Just select a timeframe in the "perd_short_desc" slicer and it will populate the bottom tables... If I need to resend the file please let me know.

Hi,

 

Still no luck.  I made a selection in the slicer but still did not see any numbers in the IYA column of the Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

 

I will re-post the link to download the file but I downloaded it from the file from the original link and made a selection on the time frame slicer and it worked fine... If you could download the orignial file again and make a selection on this slicer slicer.PNG

 

Once this selction is made it will populate the table in the bottom left... I've verified this several times...

 

Here is the new link: https://drive.google.com/file/d/1Yqix6czPTLJ981_TRB8VmxnvZLi07ftW/view

 

 

Hi,

 

This measure will rank all glb_category within a market

 

= RANKX(ALLSELECTED(POS[glb_category]),[IYA])

 

If you wish to rank the market, then remove the glb_category from the visual and write this measure

 

= RANKX(ALLSELECTED(geo[market]),[IYA])

 

You may now filter this measure by Top 5/Bottom 5.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 ?

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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.