Ranking issue with timeline filters

[Camus]

Hi!

I have a list of products that have different categories. I managed to make a formula that rank top products per category ie. every category has the top #1 product sold. Now the issue is that when I add a Year slicer (two years 2016-2017) the rankx formula does not do what I'd like to. When I choose the category from a filter and select only year 2017, the rank values in the table repeat when I choose for example year 2017 only.

Here's the formula for the RANK measure: RANK = IF(ISBLANK('powerBIdata'[Sum of Netsales]);Blank();RANKX(ALL('powerBIdata'[Product]);[KPI];;DESC;Dense))

I'd like to have ranks in normal order that take account the periods I select. Note that the KPI values change correctly with filters

Currently this is what I get:

I would really appreciate if someone finds some time to help me solve this issue 🙂 

[TomMartens]

Hey,

put your RANKX() formula inside a CALCULATE() like this CALCULATE(RANKX(...))

Hope this solves your issue

[Camus]

Thanks for the speedy reply, but it did not help. I changed my table to a Matrix, put on a view that also shows categories. What appears to be happening is that the Category itself is ranked aswell. So, obviously the KPI sum of all the categories is higher than any of the products'. So the solutions appears to be to exclude the categories from the Rank calculation. I wonder how to do that... 🙂

[Camus]

And this means that the issue is not with the time slicers, but how to exclude the categories from the calculation. What is perplexing is that how does the categories get ranked.

[TomMartens]

Hey,

can you please provide a downloadable file with sample data.

Regards

[Camus · Accepted solution]

I think I figured it out! Here's the solution:

RANK = IF(HASONEVALUE(powerBIdata[Category]);rankx(ALL(powerBIdata[Product]);[KPI]))

I have to say that DAX is a tricky thing, lots and lots to learn.

@TomMartens, Thanks for your attention!

[TomMartens]

It is 🙂

Please mark your post as answer, so others may benefit from your solution.

Cheers

[Camus]

OK, What I've noticed is that this might be a Powerbi table issue.