cancel
Showing results for
Did you mean:
Frequent Visitor

## Ranking Product basis only last month sales, across all months

Hi Guys, Needing some help with this one. I am trying to rank Products in my fact table (irrespective of the month of sale) basis its sales in the last month.

Examples of input and desired output:

 Month Product Sales June A 100 June B 50 July A 350 July B 200 Aug A 300 Aug B 400

Output

 Month Product Sales Rank June A 100 2 June B 50 1 July A 350 2 July B 200 1 Aug A 300 2 Aug B 400 1

This is a sample fact table I have. Since product B in the lastest month in my fact table has higher sales than product A, product B gets ranked 1 across time (Despite of product A having higher sales than B in June and July).

I am trying to create a stacked column chart with Months on the X axis, Sales on Y, and Product in Legend. However, I want only the top 5 selling products (in the latest month) in the legend and an "Others" category. Hence, my plan of action is:

Calculated column in the fact table which ranks all products basis their sales in latest month.

Another calculated column that goes like "If rank <= 5, Product, "Others"). <- This column I could drop in the legend and accomplish what I am trying.

2 ACCEPTED SOLUTIONS
Community Support

Thanks for the details you have provided. Try to create this calculated column that the month column is not changed:

``````Rank =
RANKX (
'Table',
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Products] = EARLIER ( 'Table'[Products] )
&& 'Table'[Month] = MAX ( 'Table'[Month] )
)
),
,
DESC,
DENSE
)``````

Attached the modified sample in the below.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

You are welcome. EARLIER() is like a loop which is mostly used in the context of calculated columns. It returns the current value of the specified column in an outer evaluation pass of the mentioned column. You can refer this document which introduces it with an example in details.

Sometimes you can also use ALLEXCEPT() to get the same result. See the below picture about this issue. But when we use a filter() function to write many filter expressions, it is better to use EARLIER()  because ALLEXCEPT() removes all context filters in the table except filters that have been applied to the specified columns.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

7 REPLIES 7
Community Support

To calculate the rank based on month, you need a actual date column to extract the month, text type of month cannot be used to rank.

``Month = MONTH('Table'[Date])``

Create this calculated column to get the last month value first:

``````last =
VAR product = [Products]
VAR _month = [Month]
VAR lastvalue =
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Products] = product
&& 'Table'[Month]
= CALCULATE (
MAX ( 'Table'[Month] ),
FILTER (
ALL ( 'Table' ),
'Table'[Products] = product
&& 'Table'[Month] < _month
)
)
)
)
VAR _sales =
IF ( ISBLANK ( lastvalue ), [Sales], lastvalue )
RETURN
_sales``````

Create the rank column:

``````Rank =
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Month] = EARLIER ( 'Table'[Month] ) ),
'Table'[last],
,
ASC,
DENSE
)
``````

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

@v-yingjl Hi,

Guess I am unable to communicate the desired output. When I say I wish to rank Products in my fact table based on latest month, I mean I need to rank them basis the very Last Month in my fact table. That means, irrespective of which month it is, in my example, I need the rank of each Product to be the same as their rank in August.

Hence, if Product A is ranked 1 in August (since it has higher sales than Product B in August), then Product A must be ranked 1 in all prior months (whether it sells higher or lower than A in those months). Once again, August here is the last month available in my fact table.

Community Support

Thanks for the details you have provided. Try to create this calculated column that the month column is not changed:

``````Rank =
RANKX (
'Table',
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Products] = EARLIER ( 'Table'[Products] )
&& 'Table'[Month] = MAX ( 'Table'[Month] )
)
),
,
DESC,
DENSE
)``````

Attached the modified sample in the below.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

This works, thanks @v-yingjl !

Could you fill me in with the use of "EARLIER" in this context?

Community Support

You are welcome. EARLIER() is like a loop which is mostly used in the context of calculated columns. It returns the current value of the specified column in an outer evaluation pass of the mentioned column. You can refer this document which introduces it with an example in details.

Sometimes you can also use ALLEXCEPT() to get the same result. See the below picture about this issue. But when we use a filter() function to write many filter expressions, it is better to use EARLIER()  because ALLEXCEPT() removes all context filters in the table except filters that have been applied to the specified columns.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Super User IV

You need it cumulative or Last month?

Product Rank = RANKX(all(Table[Product]),[previous month value])
Product Rank = RANKX(all(Table[Product]),[Cumm Sales])

with measures likes these with Date tables

MTD Sales = CALCULATE(SUM(Table[Sales]),DATESMTD('Date'[Date]))
previous month value = CALCULATE(sum(Table([Sales]),previousmonth('Date'[Date]))
Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(date,date[date] <=maxx(date,date[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :

Proud to be a Super User!

Frequent Visitor

@amitchandak hey man! As I mentioned above, I want to rank all products by creating a calculated column in my fact table. The rank for products across time is to be based on sales in the lastest month available. In my example above, that is August.

Note that Product B sells more than A only in the latest month (August) and hence it is ranked higher than Product A, across time (even though A sells more in June and July).

I could create month wise ranks, or rank the fact table using cumulative sales over the data table. I am only struggling with this piece wherein I need to replicate ranks from the latest (not last) month to all previous months.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!