Rankx with Top N driving me nuts. Please help

[cruncher]

Hi experts,

I am having a hard time finding the correct rank with TopN and filters/slicers in matrix view. My data looks like this.

These are my requirements.

1. Filter the data where Rank=Yes.

2. Create a Single select Parameter from source field excluding any blanks values.

3. Filter the data to respective source as per user selection.

4. Rank by Region and sales.

5. Add Rank, Region, Category, Include, Sales in Table/Matrix view

6. Add Include field to filters. Rank should be calculated after filtering the data.

7. Create a Top N type in parameter and show regions Rank less than Top N value.

I tried multiple things by changing the context using All and All selected, Remove filters, Calculate but not able to filter the correct topn 1 ranks after the filter/slicer selection.

Here is the pbix file with sample data PBIX link 

Please help. Many thanks in advance. Appreciate if someone invest some time to solve this.

Regards,

Ombir

[v-echaithra]

Hi @cruncher ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[FBergamaschi]

Hi cruncher, I am not sure I got what you need, can you please clarify on my below questions?

1. Filter the data where Rank=Yes.

2. Create a Single select Parameter from source field excluding any blanks values.

3. Filter the data to respective source as per user selection.

FB what do you mean in point 3.? Having a slicer with regions to be selected?

4. Rank by Region and sales.

FB Please clarify what you need to rank and by which value (es Rank Regions by Sales )

5. Add Rank, Region, Category, Include, Sales in Table/Matrix view

6. Add Include field to filters. Rank should be calculated after filtering the data.

7. Create a Top N type in parameter and show regions Rank less than Top N value.

[v-echaithra]

Hi @cruncher ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[v-echaithra]

Hi @cruncher ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @cruncher ,

Thank you for the update.

Sales Rank =
VAR _selectedSource = SELECTEDVALUE('Source Parameter'[Source])

VAR _topN = SELECTEDVALUE('Top N Parameter'[Value])

VAR _filteredTable = FILTER(ALLSELECTED(Sheet2), Sheet2[Include] = "Yes"&& Sheet2[Rank] = "Yes"&& Sheet2[Source] = _selectedSource)

RETURN
IF (Sheet2[Include] = "Yes" && Sheet2[Rank] = "Yes"&& Sheet2[Source] = _selectedSource,
RANKX(_filteredTable,[Sales $],,DESC,DENSE))

,and a measure to filter top n results

Show_Top_N =
VAR _topN = SELECTEDVALUE('Top N Parameter'[Value])
RETURN
IF([Sales Rank] <= _topN, 1, 0)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[maruthisp]

Hi cruncher,

As always DAX is harder to understand when compared with writing SQL queries. I would suggest below for you:
1.First come up with a sql query to retrieve the result as per your business logic.
2.Second thing is translate the same logic in to DAX expression
3.It will be easy to understand SQL and DAX simultaneouly how it works and behave.
4. As you know there is no single solution for problem to solve. You can come up with multiple approaches to solve the same problem. 
5. Be cool 😉
Rankx.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[cruncher]

Thanks for looking into it. It returns wrong rank and Top N is not working. Please check

[adudani]

hi @cruncher ,

Does this resolve the issue?

[cruncher]

Thanks for looking into it. It returns wrong rank.

Can you check and also include Top N parameter to filter the rank