Forum Discussion
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 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.
-----I have tested this measure in both ways----
1.
2.
Then, i create this mesure to put in the visual filter in the graph:
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
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.
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.
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:
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.
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:
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:
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:
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!
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.
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
Anonymous , Create the second measure like and try
calculate(if(<Condition>) , values(Datos[Asset Class]))
Refer this I have used Rank filter there.
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=dcc1870c-76e0-4f83-b4dd-ab08f30c5799&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLXVrLXNvdXRoLXJlZGlyZWN0LmFuYWx5c2lzLndpbmRvd3MubmV0LyJ9Anonymous
Can you pease share the PBIX file itself? (through Onedrive, Dropbox, Google Drive..)
