top n slicer problem in matrix

Question

Hi experts, I created a topn slicer to get the topn dynamically, it works well when there is no value in column in matrix and works well in table, but when I expand the dimension in column in matrix, it fails to return the n rows i selected

so how to solve it?

the rank that I calculated:

RANKX(

ALL(dim_staff[gpn])

,[cal_amount_PPEDD],

,DESC,Dense

)

works well when column not dragged:

fails to return selected n rows required:

here is the method which I created topn:
https://youtu.be/vm2mdEioQPQ

anonymous · Answer

Hi&nbsp;oakfootballclub&nbsp;,
The problem might be due to the filter context of the visual. When you expand the dimension in the matrix, the filter context changes, which might affect the ranking.&nbsp;Could you please&nbsp;provide some&nbsp;raw data&nbsp;in the table 'dim_staff'&nbsp; (exclude&nbsp;sensitive&nbsp;data) with&nbsp;Text&nbsp;format&nbsp;and your&nbsp;expected result&nbsp;with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
&nbsp;
And&nbsp;it is better&nbsp;if you can share a&nbsp;simplified&nbsp;pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
&nbsp;
In addition, you can refer the following links to get it.
Filter Top N Values with a Slicer in Power BI&nbsp;
Solved: Top N Slicer in a matrix? - Microsoft Fabric Community
Best Regards

oakfootballclub · Answer

Thank you, the data is quite straightforwad, I reviewed the raw data and it just exactly the same as the following shows:dim_staff&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gpn&nbsp;bu&nbsp;sl&nbsp;67831&nbsp;MKT&nbsp;MKT1&nbsp;30277&nbsp;MKT&nbsp;MKT2&nbsp;45111&nbsp;SALES&nbsp;SALES1&nbsp;47039&nbsp;SALES&nbsp;SALES2&nbsp;58663&nbsp;SALES&nbsp;SALES3dim_category&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;id&nbsp;category&nbsp;subcategory&nbsp;10&nbsp;A&nbsp;A1&nbsp;20&nbsp;A&nbsp;A2&nbsp;11&nbsp;A&nbsp;A3&nbsp;10&nbsp;A&nbsp;A4&nbsp;13&nbsp;B&nbsp;B1&nbsp;14&nbsp;B&nbsp;B2fact_table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;category_id&nbsp;staff_gpn&nbsp;amount&nbsp;17&nbsp;63651&nbsp;446658&nbsp;10&nbsp;47463&nbsp;682308&nbsp;11&nbsp;62831&nbsp;390495&nbsp;13&nbsp;46428&nbsp;677903&nbsp;15&nbsp;31168&nbsp;487138&nbsp;11&nbsp;54683&nbsp;590078

anonymous · Answer

Hi&nbsp;oakfootballclub&nbsp;,
Based on this sample data you've provided, what do you ultimately want to look like? Is it possible to create a PBIX file with the data you provide, which contains the sample data and the results you want? You can refer the following link to upload the pbix file in the community. Thank you~
How to upload PBI in Community
Best Regards

oakfootballclub · Answer

Hi, here is the example that I want to get, the problem is just as I described that when I deagged the expen_type to the column of matrix, it fails to show the actual top n based on the top n slicer; when I remove the expens_type from column the function works well, but I need to add the expens_type in to column of matrix to analyze&nbsp;&nbsp;

oakfootballclub · Answer

Anonymous&nbsp; Hi, you can just use the data I show above and the measure I created:amount_rk=RANKX(&nbsp; &nbsp;&nbsp;ALL(dim_staff[gpn])&nbsp; &nbsp; ,[amount],&nbsp; &nbsp; ,DESC,Dense)and then add this measure to filter:IF([amount_rk] &lt;= SELECTEDVALUE(dim_topxslicer[col]),1,0)&nbsp;and the problem is that when I drag the category dimension into column of matrix, it fail to get expected top n based on slicer.

Forum Discussion

top n slicer problem in matrix

5 Replies

Related content

Matrix Visual Hierarchy Level Problem

Slicer + Matrix

It's a matrix totalling problem!

Problem with field parameter and empty slicer

Slicer Problem

dim_staff
	gpn	bu	sl
	67831	MKT	MKT1
	30277	MKT	MKT2
	45111	SALES	SALES1
	47039	SALES	SALES2
	58663	SALES	SALES3
dim_category
	id	category	subcategory
	10	A	A1
	20	A	A2
	11	A	A3
	10	A	A4
	13	B	B1
	14	B	B2
fact_table
	category_id	staff_gpn	amount
	17	63651	446658
	10	47463	682308
	11	62831	390495
	13	46428	677903
	15	31168	487138
	11	54683	590078