Ranking Product basis only last month sales, across all months

[Anonymous]

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

Some more information: 

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. 

Thanks in advance!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

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.

[v-yingjl]

Hi @Anonymous ,

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
)

Attached a sample file in the below, hopes to help you.

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.

[Anonymous]

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

[v-yingjl · Accepted solution]

Hi @Anonymous ,

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.

[Anonymous]

This works, thanks @v-yingjl ! 

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

[v-yingjl · Accepted solution]

Hi @Anonymous ,

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.

[amitchandak]

@Anonymous ,

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]))
last MTD Sales = CALCULATE(SUM(Table[Sales]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

[Anonymous]

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