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

12 REPLIES 12
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

Anonymous
Not applicable

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)

@SqlJason how would you be able to keep the others at the botom of the table?

Eg.,

NameValue
Name 1  100
Name 2  90
Name 3  40
Other   80

 

In this case one would need an assistance column for the shorting part. An idea could be to have a calculated index column based on the Value. However, how to do it since Value is not have a calculate dcolumn but a measure?

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.

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