Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 
 

Captura.PNG

 

I want to filter the entire page by the Top N Managers wich has more AuM in order desc. I Have reviewed all the youtube videos that explain how to use the top N and have not found a possible solution. I have worked with different measures in many different ways but have not found anything that could help me. 

Moreover, I have downloaded examples from Avi Singh who is a very good teacher. I think I'm close to the solution but I can't finish.

Example of the first graph above left and measurements that I have created.

1.PNG
3.PNG

-----I have tested this measure in both ways----
1.

2.PNG

2.5.PNG

 

Then, i create this mesure to put in the visual filter in the graph:

6.PNG

 

7.PNG
I think I'm doing complicated things and I'm making a mess, and I think it must be easier than I think.

Any help is welcome, many thanks to everyone¡¡

Best regards

 

17 REPLIES 17
DataZoe
Employee
Employee

@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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

I have created a new proxy to share in order to someone can help me:

https://app.powerbi.com/reportEmbed?reportId=c6c529fe-ec4f-4265-9ae9-905c09bb6312&autoAuth=true&ctid...

@Anonymous 

 

Can you pease share the PBIX file itself? (through Onedrive, Dropbox, Google Drive..)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

@Anonymous 

 

See if this works for you (I wasn't sure what calculation you wanted to see in the visual "TopN sobre Income Total"). I've changed the TopN range to 1 - 4 (assuming the rank refers to "Shops") since there are only 4 possible in the sample data.

The data is showing the values for the TopN Shops overall (ie. not ranked for each category in the visuals)

 

Overall Rank filter.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

That is exactly what I need, thank you very much, I do not know how to thank your help. 

I only have one problem with the data table, since I had to create another measure to order the shops / managers by AuM desc. The calculation of the total of the table does take into account the filtered Top N, but it shows all the Manager / shops of my table and I just want it to show me:

-If I choose 5: the first 5 managers
-If I choose 10: the top ten

11.PNG
So i created a new measure for tank ONLY for the table:

13.PNG


12.PNG


I had to create this new measure because the measure that you have used returns only the number 1 for all shops / managers as seen below:

14.PNG

@Anonymous 

Sorry, I'm not sure what you mean in your last post (or who you are referring to...!)
If you are referring to the solution I suggested, you can limit the list of Shops/managers in the table visual by using a measure such as:

 

 

Selected RankX = IF([RankX Shops Overall] <=[Selected N], [RankX Shops Overall])

 

 

To give you this:

Selected rankx.JPG

 

Ps.
1) The example you posted to solve this seems wrong, since you are filtering by Top 10 and you are getting the 11 top managers (if not more! Which means something is wrong with the [Overall Value] calculation too BTW - please check the Dimension Tables in the sample PBIX file I attached and how they are used in both the measures AND visuals AND the actual model relationships).

To solve this, if you want to use your measure, try changing  the [value] expression in the new rank measure with [Overall value]

2) If you want to get rid of the Rank value in the "Total" row in the table, use the ISINSCOPE function (sorry, I'm not in front of my pc so I cannot be more precise. if you need help with this, just let us know!)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Yes, sorry for my english it's not good that i would.

The problem is that if i click 5 on this filter:

15.PNG
I only want to see in the table, the top 5 shops/managers with more Aum. If i click 10, i only want to see in the table top 10 managers/shops with more Aum

And the result that this table shows is:
16.PNG

There are all shops/managers in the table (210 more or less). But, Overall value IS CORRECT because that 39.879.306... its the sum of the 5 top shops, but the table shows all shops





@huentala94

Can you post the measures you have used to calculate the [Overall Value]? In the example I provided, the rows beyond the selected TopN are blank, so there os something going on in your measures





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

17.PNG

18.PNG

19.PNG

20.PNG

21.PNG

22.PNG

@Anonymous 

Thanks for the file.

A couple of questions:

1) So when you want to flter by TopN managers, which field determines "Manager"? Is it "Shop"?

2) When you wish to filter by the TopN, is the TopN for the whole dataset (so when you show the results by for example "Asset Class", the TopN refers to the overall performance and not the TopN within "Asset Class") , or is each visual to represent the TopN for the category shown in the visual? - see my example in my previous post.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

The top N refers to the managers with the most assets under management. That is to say:

-If I filter by Top 5 I want the entire page to be filtered with the data of the 5 managers with the most assets under management, I do not want data from other managers to appear that are not that top 5

-If I filter by Top 10 I want the entire page to be filtered with the data of the 10 managers with the most assets under management, I do not want data to appear from other managers that are not that top 10

In the case of the graph: AuM by Asset Class I want the filter on selector 5 or 10 or 30 .. that graph shows the AuM of those selected managers filtered by Asset Class.

In summary, I want both the top 3 letters on the right, as well as the table, and the different graphs ONLY to show me the data of the Top N filter managers that I have selected. And that data is the relationship between the AuM column and different columns with other categories

I have created a proxy eliminating the real data and eliminating columns to be able to share the powerBI and the equivalences are the following:
Shops = Managers
Seller = Fund
Sales = Aum

Thank you very much for the great help



v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

You might consider providing your dummy pbix that would be folpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

I have created a proxy of a data table similar to the one I use (although with much less categories). My idea is to create a dashboard page in which I can filter by Top Shops: 5-10-20-50 based on the shops that have had AuM 4Q/20. I would like all the graphics to be filtered by said top N shops.

10.PNG

9.PNG

 

I think the problem is that i dont know how to create new measures to filter by top N shops by AuM ( DESC), in each graph.

Thanks everyone again¡¡.

@Anonymous 

 

I think I understand what you are trying to achieve: filter all visuals on the page by using one report page filter, right?

If so, you need to include a filter statement (or reference statement to the "TopN" selection) in all the measures in each visual. Otherwise the visuals will not be filtered by the global filter; only the visuals which  include the "Shop" Dimension as a filter context will get filtered; all others will not. 

In the following example, I've created a meaure to rank Items.

First, the model itself:

Filtr by rank model.JPG

 

I want to see only the actuals and target for the top 3 items dimension.

As you can see, if a visual does not contain the Item dimension, the rank returned is "1" for all the other dimensions ("Channel" and "REF Dimension"). These visuals will therefore not be filtered by a page filter referencing a "TopN" Items; you need to specify the Filter context in the measures used.
wrong item rank.jpg

 


In my case I have created measures as follows:

To filter each dimension by its top 3 items (so the result would respect the top 3 items per each dimension), I use the following RankX measure:

 

RankX actuals by item = RANKX(ALL('Item Dim'), [Sum of Actuals],,DESC,Dense)

 

Which Gives me this:

rank by item.JPG

 

If, however,I need the top 3 Items for all dimensions (Ie the top 3 of all), I would use this measure:

 

RankX actuals by item (ALL dimensions) = CALCULATE([RankX actuals by item], ALL('Channel Dim'[Channel]), ALL('Ref Dim'[REF]))

 

Which gives me this:

rank items all.JPG

 

You can then use either of these ranks in the filter expression of measures. For Overall 3 Top Items:

 

Actuals Filtered by RANK Top 3 Items = CALCULATE([Sum of Actuals], 
FILTER('Item Dim', 
[RankX actuals by item (ALL dimensions)] <=3))
Target Filtered by RANK Top 3 Items = CALCULATE([Sum of Target], 
FILTER('Item Dim', 
[RankX actuals by item (ALL dimensions)] <=3))

 

Check the results:

Filter by rank all.JPG

 

If you look at the visuals which do not have the Items Dimension as a filter context, the rank returned for all rows is "1". The global filter would not work. You get around this by using measures which include the relevant filter context for the dimension you wish to filter by, if that makes sense (see my sample measures). When you include these measures with other dimensions, you will get the correct results (in the example, it's the columns and values in the tables/matrix with green backround, where you get the result for the top 3 overall Items and how much the contribute to the Channel or Ref Dimensions).

In your case, since you want it to be dymanic, you need to substitute the "<=3" in the filter expression in the measures with the SELECTEDVALUE expression for the TopN slicer value.

I hope that makes sense!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Thank you very much for the full explanation, but i dont know what i'm doing wrong, i'm gonna share a powerBI proxy and put the link here to see if someone can help me because I've already tried everything.

amitchandak
Super User
Super User

@Anonymous , Create the second measure like and try

calculate(if(<Condition>) , values(Datos[Asset Class]))

 

Refer this I have used Rank filter there.

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.