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

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

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



Highlighted
Super User II
Super User II

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

@huentala94 

 

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.






Highlighted
Super User I
Super User I

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

@huentala94 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-2f... 

 

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

Highlighted
Helper I
Helper I

k youRe: Top N filter does not work (with examples)

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

Highlighted
Super User II
Super User II

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

@huentala94 

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.






Highlighted
Helper I
Helper I

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

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





Highlighted
Super User II
Super User II

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

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






Highlighted
Helper I
Helper I

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

17.PNG

18.PNG

19.PNG

20.PNG

21.PNG

22.PNG

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