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

Dynamic Top N and Others

I have a problem with implementing a donut showing top N and Others groups for a measure. My underlying dimension data is of below type:

ProductGroup
1A
2A
3A
4B
5B
6B
7C
8C
9C
10C

 

It has a measure connected on Product column. I need to get the top N [Group] as shown below where the blue segment is "Others" group. 

Donut chart

The problem is with naming the "Others" as when I am doing it in another calculated column, it is static and doesn't work well when any filters are applied on the report. Is there any way to make it responsive to filters, so that respective Top N [Group] are shown for different selection ?

1 ACCEPTED SOLUTION
SqlJason
Memorable Member
Memorable Member

The key to it woyld be to use a calculated table with an extra member for Others. I recently wrote a similar blog 

http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

 

In this case, the measure in my blog will have to be replaced by the TopN part. Let me know if you have difficulties applying this and if yes, I can explain in more detail (especially if you can give sample data and show the results also, so that I can put it together in apower bi file)

View solution in original post

11 REPLIES 11
Nancyjain
Helper I
Helper I

Hello Everyone

I have a requirement of showing a slicers which have Top N and Button N . When I click on Top N all my charts should show Top N values and when I click on Bottom N values all charts should change to Bottom N values.

 

Any help will highly appreciated.

Thank You

Nancyjain - 

While I don't think you can parameterize the "Top/Bottom" setting in a TopN filter you could accomplish this by creating the visuals for both the Top and Bottom filters on the page and then use Bookmarks - have one Bookmark that displays the Tops and one that displays the Bottoms.  Then use images and Actions to navigate between the bookmarks to give your report that app-like feel.

Just a thought.

Eric

 

 

Thank you so much for your response.

 

I have solved this problem by using bookmark and toggle buttons. 

 

cheers

Nancy

If its not too many measures, then you can also create a custom measure inDAX, where you can do a check if the selected value is "Top" or "Bottom" and calculate for each case.

SqlJason
Memorable Member
Memorable Member

The key to it woyld be to use a calculated table with an extra member for Others. I recently wrote a similar blog 

http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

 

In this case, the measure in my blog will have to be replaced by the TopN part. Let me know if you have difficulties applying this and if yes, I can explain in more detail (especially if you can give sample data and show the results also, so that I can put it together in apower bi file)

View solution in original post

I am working on a project. I need to create a dynamic top n which should work when different slicers are used. I used RankX which gives me the results i need when i group on only one column, eg clientgroup, but the issue arises when i add different columns, such as ClientGroup, SalesLocation, SalesPersonName, HoursWorked. The results is not right when i apply different slicers.  I tried to create a column for rownumber so that i can get the dynamic topn, that is if the row number can default to start from 1 with increment of 1 whenever any filter is applied. But this is becoming more difficult than i thought. Can anybody help. Urgent please. I have spent several days on this but to no where

You would need to calculate the top n measure against the low level attribute you are showing it against. The filters would work just fine in that scenario. You cannot do it with calculated columns as its impossible to map out every scenario.

ame54
Frequent Visitor

I am facing the same case where i want to show a bar chart with Top 5 sales but having Others as alwasy in the 5 elelmtns on the chart. 

I have tried code from your blog and it is working with a slicer fine, but i am not sure how to swithc it to my case where i alwasy want to show the Top 5 with others in them, as in not dependent on user selection in the slicer.

 

Any help on this is appreciated 

it is possible to share the file 

Thanks Jason! I checked out the method on a smaller dataset and it works well. But, it is unable to calculate the visual in my model, possibly as its a lot bigger in size.

vaibhav_osc
Helper I
Helper I

I have a problem with implementing a donut showing top N and Others groups for a measure. My underlying dimension data is of below type:

ProductGroup
1A
2A
3A
4B
5B
6B
7C
8C
9C
10C

 

It has a measure connected on Product column. I need to get the top N [Group] as shown below where the blue segment is "Others" group. 

Donut chart

The problem is with naming the "Others" as when I am doing it in another calculated column, it is static and doesn't work well when any filters are applied on the report. Is there any way to make it responsive to filters, so that respective Top N [Group] are shown for different selection ?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors