* add a measure that we use for ranking */ VAR Items = SELECTCOLUMNS(ALL(Subcategory_wOthers), "RankItem", Subcategory_wOthers) * get the items for which we want to calculate TopN + Others */ I further use ALLNOBLANKROW() to remove to get rid of any blank rows. I used ROW() first so I can specify the new column names directly. I used a DAX calculated table that does a UNION() of the existing rows for the TopN calculation and a static row for “Others”. These work fine even if I do not like the DAX as it is unnecessarily complex (from my point of view) but the general approach is the same as the one that will I show in this blog post and follows these steps:ġ) create a new table in the data model (either with Power Query or DAX) that contains all our items that we want to use in our TopN calculation and an additional row for “Others”Ģ) link the new table also to the fact table, similar to the original table that contains your itemsģ) write a measure that calculates the rank for each item, filters the TopN items and assigns the rest to the “Others” itemĤ) use the new measure in combination with the new table/column in your visual Step 1 – Create table with “Others” row Power BI community – Dynamic Top N and Others category As you probably expected, also this has been covered already:
It has to be part of the data model and as the TopN calculations is already dynamic, also the calculation for “Others” has to be dynamic. In Power BI we cannot simply add a new “Others” row on the fly. However, they usually will not understand the complexity behind this requirement for Power BI. For regular business users this requirement sounds really trivial because in Excel the could just add a new row and subtract the values of the TopN items from the Grand Total. I have seen two scenarios that cause confusion:ġ) The Total is the SUM of the TopN items only – not reflecting the actual Grand TotalĢ) The Total is NOT the SUM of the TopN items only – people complaining that Power BI does not sum up correctlyĪs I said, this pretty much depends on the business requirements and after discussing that in length with the users, the solution is usually to simply add an “ Others” row that sums up all values which are not part of the TopN items. This depends on how you implemented the TopN measure and what the users actually expect. Once you have implemented this change the business users usually complain that Total is wrong. SQL BI – Use of RANKX in a Power BI measureįourMoo – Dynamic TopN made easy with What-If ParameterĪgain, this works pretty well and is explained in detail in the blog posts.
So they implement a slicer and make the whole calculation dynamic as described here: The next requirement that usually comes up next is that the customer wants to control, how many Top items to show. This is very straight forward and I do not think it needs any further explanation.
table visual) to only who 5 rows and sort them descending by your measure: The easiest thing to do is to simply resize the visual (e.g.
#Drill down function not working flowjo 10 how to
A very common requirement in reporting is to show the Top N items (products, regions, customers, …) and this can also be achieved in Power BI quite easily.īut lets start from the beginning and show how this requirement usually evolves and how to solve the different stages.