Forum Discussion

dentonblake's avatar
dentonblake
Helper II
8 years ago
Solved

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)     Appreciate all insight & ...
Need Help
  • Rfranca's avatar
    Rfranca
    8 years ago

    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

     

dentonblake's avatar
dentonblake
Helper II
8 years ago

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).

 

 

Thank you for all of your help!

 

~db

Rfranca's avatar
Rfranca
Resolver IV
8 years ago

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