Dynamic Top N and Others

[vaibhav_osc]

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:

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

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. 

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 ?

[Nancyjain]

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]

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

[Nancyjain]

Thank you so much for your response.

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

cheers

Nancy

[vaibhav_osc]

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 · Accepted solution]

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)

[tschris]

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

Eg.,

Name	Value
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?

[aaarmstee67]

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

[vaibhav_osc]

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]

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 

[mdaamirkhan]

it is possible to share the file 

[vaibhav_osc]

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]

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:

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

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. 

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 ?