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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
dentonblake
Helper II
Helper II

Top 50 Rank

Have a table with sales for specific restaurants - would like to rank top 50 by  'xMonthActual_TABC' sales and 'xYearActual_TABC' sales.  (see screenshot below)

 

Capture-6.JPG

 

Appreciate all insight & recommendations!  ~db

1 ACCEPTED SOLUTION

That's correct.

This is because rank was created with two columns, [location_name] and [location_city].

When you select a city, it does the rank considering the two columns. To work as you wish, consider only the [location_name] column in the rank and put the [location_city] column as the filter.

 

To do this, change the two measures.

Rank MonthPrevius = IF (
     HASONEVALUE (ft_SALES [location_name]);
     RANKX (ALL (ft_SALES [location_name]);
         [xMonthPreviusToDate_TABC] ;; DESC; DENSE))


Rank MonthActual = IF (
     HASONEVALUE (ft_SALES [location_name]);
     RANKX (ALL (ft_SALES [location_name]);
         [xMonthActual_TABC] ;; DESC; DENSE))

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

 

Best Regards,

Rfranca

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hello Guys

 

I need to create 2 ranking

 

  • Add graph for Top 5 largest increases to the number of times a Category is linked
  • Add graph for Top 5 largest decreases to the number of times a Category is linked

Can anyone help me with it?

Rfranca
Resolver IV
Resolver IV

HI, @dentonblake

 

LET'S CREATE A MEASURE
1. Create the total values ​​you want to rank (in this case they are already created xMonthPreviusToDate_TABC, xMonthActual_TABC). If you apply in other examples, just calculate something like
Total_Sales_Last_Year = SUM (ft_SALES [total_LY])
But let's use the ones you already have.

 

2. Create the two measures to rank the values ​​in this case
we are ranking by the two columns [location_name] and [location_city] here you can put one only. 

Rank MonthPrevius = IF (
    HASONEVALUE (ft_SALES [location_name]);
    RANKX (ALL (ft_SALES [location_name]; ft_SALES [location_city]);
        [xMonthPreviusToDate_TABC] ;; DESC; Dense))


Rank MonthActual = IF (
    HASONEVALUE (ft_SALES [location_name]);
    RANKX (ALL (ft_SALES [location_name]; ft_SALES [location_city]);
        [xMonthActual_TABC] ;; DESC; Dense))

3. Create a measure to rank 25 item (50 is a lot, but you can change it) 

Top 25 MonthPrevius = IF ([Rank MonthPrevius] <= 25; [xMonthPreviusToDate_TABC]; blank ())

Top 25 Rank MonthActual = IF ([Rank MonthActual] <= 25; [xMonthActual_TABC]; blank ())


4. Create a ranking presentation measure.

Position MonthPrevius = IF ([Rank MonthPrevius] <= 25; [Rank MonthPrevius]; BLANK ())

Position MonthActual = IF ([Rank MonthActual] <= 25; [Rank MonthActual]; BLANK ())

 

5. Create another table to display the result place the columns
[Location_city], [location_name], [Position MonthPrevius], [Top 25 Rank MonthPrevius], [Position MonthActual], [Top 25 Rank MonthActual]

 

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

 

Best Regards,

Rfranca

Rfranca-

 

Works great when ranking across all cities - but, if I want to rank the Top 25 (or 50) in one city (e.g. Dallas) then it doesn't work (see below).

 

Capture-8.JPG

 

Thank you for all of your help!

 

~db

That's correct.

This is because rank was created with two columns, [location_name] and [location_city].

When you select a city, it does the rank considering the two columns. To work as you wish, consider only the [location_name] column in the rank and put the [location_city] column as the filter.

 

To do this, change the two measures.

Rank MonthPrevius = IF (
     HASONEVALUE (ft_SALES [location_name]);
     RANKX (ALL (ft_SALES [location_name]);
         [xMonthPreviusToDate_TABC] ;; DESC; DENSE))


Rank MonthActual = IF (
     HASONEVALUE (ft_SALES [location_name]);
     RANKX (ALL (ft_SALES [location_name]);
         [xMonthActual_TABC] ;; DESC; DENSE))

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

 

Best Regards,

Rfranca

 

Rfranca, thank you again ... you're my hero!  ~db

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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