Top 50 Rank

[dentonblake]

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 & recommendations!  ~db

[Anonymous]

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]

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

[dentonblake]

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

[dentonblake]

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