Forum Discussion
Advocate IDynamic 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:
| 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 ?
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)
12 Replies
vaibhav_oscI 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 ?
- SqlJason
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)
- 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.
- ame54
Advocate II
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
Post Prodigy
it is possible to share the file
aaarmstee67Helper I
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.
NancyjainHello 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
- 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.
