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

MonthProduct Sales
JuneA100
JuneB50
JulyA350
JulyB200
AugA300
AugB400

 

Output

Month ProductSalesRank
JuneA1002
JuneB501
JulyA3502
JulyB2001
AugA3002
AugB4001

 

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!

2 ACCEPTED SOLUTIONS

Hi @Sid_PowerInBI ,

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
)

month rank.png

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.

View solution in original post

Hi @Sid_PowerInBI ,

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.

col.png

 

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.

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi @Sid_PowerInBI ,

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
)

rank.png

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.

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

Hi @Sid_PowerInBI ,

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
)

month rank.png

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.

View solution in original post

This works, thanks @v-yingjl ! 

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

Hi @Sid_PowerInBI ,

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.

col.png

 

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.

View solution in original post

Super User IV
Super User IV

@Sid_PowerInBI ,

 

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.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors