Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Top N Filter doesn't work (with examples)

Hi everyone¡ due to the good help I received with my previous problem with PowerBI, I have decided to ask for help again.       I want to filter the entire page by the Top N Managers...
Need Help
DataZoe's avatar
DataZoe
Microsoft Employee
6 years ago

Anonymous I actually did a presentation on how to do this a couple months ago, it may help you with your solution.

 

My solution is actually a top/bottom x (shows top x and bottom x on the same chart) but can easily be modified to do only top x.

 

https://www.pbiusergroup.com/viewdocument/self-service-bi-tips-topbottom-da?CommunityKey=03d8ab87-2fb7-4875-b3e1-f9b4e2396e19&tab=librarydocuments&LibraryFolderKey=ab96131b-d53a-42fd-951a-aebc45a88ec4&DefaultView=folder 

 

This is the ranking measure: 

 

Rank (Desc) of Managers by Value = 
IF (
	ISBLANK ( [Value] ),
	BLANK (),
	RANKX (
		FILTER (
			ALLSELECTED ( Datos[Manager] ),
			ISBLANK ( [Value] ) = FALSE ()
		),
		[Value],
		,
		DESC,
		DENSE
	)
)

 

 and then you create a flag measure to work with your selected "X":

 

Rank Flag for Value = 
IF (
	ISBLANK ( [Value] ),
	BLANK (),
	IF (
		[Rank (Desc) of Managers by Value] <= [Selected N]),
		1,
		0
	)
)

 

Then you put the [Rank Flag for Value] as a FILTER FOR THIS VISUAL on the bar chart visuals.

 

This is also shown here: https://community.powerbi.com/t5/Data-Stories-Gallery/Executive-Insights-by-Decisive-Data/m-p/181742