cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dentonblake Regular Visitor
Regular Visitor

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

Accepted Solutions
Rfranca Member
Member

Re: Top 50 Rank

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

 

5 REPLIES 5
Rfranca Member
Member

Re: Top 50 Rank

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 Regular Visitor
Regular Visitor

Re: Top 50 Rank

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

Rfranca Member
Member

Re: Top 50 Rank

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

 

Highlighted
dentonblake Regular Visitor
Regular Visitor

Re: Top 50 Rank

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

DECOVIOTI Regular Visitor
Regular Visitor

Re: Top 50 Rank

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?