Forum Discussion

Dalla_Terra's avatar
Dalla_Terra
Helper I
5 years ago

Rank by Current Year Using Multiple Dimension and Multiple Years

I am hoping someone can help me out with this. I feel like I am close but just not getting the results I need.    I need to create a display that ranks top brands by sales but I want to see how the...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

Dalla_Terra , Not very clear on expected output. Can you share that.

As of now, your current formula will calculate Rank for each year. So not sure what you mean by driven by last year. Do you need the top 10 brands of the current year as filter?

 

This will give continuous rank

Brand Rank 9L =
     Var BrandRank = RANKX(

          ALL(Brand[Brand Name], Date[Year]),[Sales 9L],,DESC)
RETURN
BrandRank

 

Sales 9l  = sum(Depletion[Sales 9L])

 

 

Dalla_Terra's avatar
Dalla_Terra
Helper I
5 years ago

Thanks for your reply, yes, I am trying to display the rank by the most current year but also show the performance for the years prioir as well.

 

Let me try to clarify. 

 

I am using this calculation to create a bar graph. I would like to display a ranking of the top 10 products on a page with the performance of each product by year but would like to have them sorted so that the most current year (2021) is the sort order. 

 

In my graph below, green represents the current year (2021) but as you can see the first product ranks lower than the second in the year 2021. There are others products that are out of order as well. 

 

The table I shared shows what the output is currently and the graph below shows what that looks like visually. 

 

I can't seem to find a way to sort the ranking by the most current year.