Ranking for a product - new ranking for each year

[DavidWaters100]

Hi,  I have gone through a lot of answers to questions on here about Rank X, but cannot apply any of them successfully to my scenario.

I have a product category, sales and dates (year).  What I need to do is return the rank of the product for each year (independent of any other years).  So the ranking of a product for any given year only.

I wanted to build a table which showed each product in rows and the ranking position for each year in columns.  But struggling to get this to work!

Any help greatly appreciated...thanks

[amitchandak]

@DavidWaters100 , You can have sub category Rank with year. For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[DavidWaters100]

Hi @amitchandak, thanks for the links - I already looked at these but could not get the scenario to work at the moment!

[amitchandak]

@DavidWaters100 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[DavidWaters100]

Hi @amitchandak 

I have created a dummy file with the same problem and the drop box link is below.  The expected result should be possible but I cannot get it to work!

https://www.dropbox.com/preview/Top%20Ten%20problem%20example.pbix?role=personal

I have a measure which is returning amounts for the top ten companies in April this year and putting the rest in "other". I then need to return the amounts for last year - for the same top ten companies in this year. The measure for the previous year correctly returns last year amounts where they exist, but instead of putting the rest in "Others" it lists them in an "extended" Top ten category. The "Other" total is wrong too.

Per below - gold is correct top ten, and last years values for this top ten are returned. But I was expecting the amounts for companies S to Z to fall in the "other" category. Maybe there is a simpler way to do this but I have repeatedly tried and failed!

For now I have fudged the result using Top N filter and created a separate visual for "other" and arranged them together so it looks like they are part of the same visual but it's messy and the columns do not line up perfectly and it renders very slowly in the app! I really need to create this in one neat visual.

If you have any suggestions that would be greatly appreciated. thanks

[DavidWaters100]