cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Super User IX
Super User IX

Re: Top N Filter doesn't work (with examples)

@huentala94 , 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-wi...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Top N Filter doesn't work (with examples)

Hi @huentala94 

 

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.
Highlighted
Helper I
Helper I

Re: Top N Filter doesn't work (with examples)

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¡¡.

Highlighted
Super User II
Super User II

Re: Top N Filter doesn't work (with examples)

@huentala94 

 

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.






Highlighted
Helper I
Helper I

Re: Top N Filter doesn't work (with examples)

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.

Highlighted
Helper I
Helper I

Re: Top N filter does not work (with examples)

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...

Highlighted
Super User II
Super User II

Re: Top N filter does not work (with examples)

@huentala94 

 

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.






Highlighted
Helper I
Helper I

Re: Top N filter does not work (with examples)

Highlighted
Super User II
Super User II

Re: Top N filter does not work (with examples)

@huentala94 

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.






Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors