cancel
Showing results for
Did you mean:
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)

Appreciate all insight & recommendations!  ~db

1 ACCEPTED SOLUTION

Accepted Solutions
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))```

Best Regards,

Rfranca

5 REPLIES 5
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]

Best Regards,

Rfranca

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

Thank you for all of your help!

~db

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

Best Regards,

Rfranca

Highlighted
Regular Visitor

## Re: Top 50 Rank

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

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?