Laura Collie on 22 Apr 2017 00:21:19
Top n Filter should be allowed on multiple fields and/or against multiple facts. For example - show top 10 sales people and lowest 10 products (both facts should be true to be displayed). As of April 2017, only one top n filter is allowed, so I'm exporting data to Excel for reporting.
Also, wish Excel could use my .pbix file as a data source for the PowerPivot data model - then i could easily work with any of the data (including all the measures). Power BI is a fantastic tool, but still needs Excel at times.
- Comments (99)
RE: Top n filter
Add ability to have slicer use top n rows
RE: Top n filter
'I have been using Top N feature on Power BI ever since it was released however, I realised today that it does not work well if applied along with other measure filter on the same grid. Basically, the Top N function is applied before any other filter hence there are cases when this returns less than N rows. Here is how you reproduce:
- Take a matrix visualization. Put couple of fields on rows and few measures in values.
- Take any column that is on row and apply top N filter for any measure you desire.
- Now take another measure that is visible and apply an advance filter where value > 100 or value is < 0 etc.
- What you will notice is the top N filter is applied first and then the second filter whereas it should filter the second one first and then applying top N filter.
Make sense?
RE: Top n filter
I would love a new "dynamic" grouping capability that would display a Categorical Column showing the Top n values and then all the other grouped as "Other": I would select the field to group, the formula to calculate (Count, Sum etc.) the ranking and the threshold (i.e. Top n, % of Total or absolute value) (this is similar to the Top n Filtering capability, but it keeps ALL data in the Totals etc.) (Note: the Sundance Visual automatically creates such Other category, but without a sort capability)
Rationale:
Quite often there is a long list of items where only a small number account for 80% of the variable to explain (i.e. 20% of Products account for 80% of Sales) . I find it quite difficult to generate any report where the top 20% products would appear by name and all the other would be grouped as 'Other':
• The new Group feature requires that I create (manually) a separate group for EACH of the 20% top products, then an "Other" group for the 80% remaining and it still displays each product in some reports.
• I can generate a new Table with the Summarize and Filter Functions that would contain only the Top 20 product names by Sales and link it (with a one to many relation) to the main Table: however, when I report, the "other" products appear as a blank line.
• In fact, I need to create a new Table with two columns: the original Product name, and the "Grouped Product" one which takes the value of the product name for the Top 20 and the value 'Other' for the remainder of the list (this involves several steps , usually include the RANKX function etc.). Then I have to relate it to the data Table. And even then there are issues such as 'blank' values etc.
Note: since all this is mostly for reporting purposes, this could be implemented initially in a Filter Visual or in the Table or Matrix Visual by modifying the Top N filtering capability
RE: Top n filter
For create a table of the stores with more seals, or the products liked for the people.
RE: Top n filter
So I think I may have found some kind of workaround to my problem but not entirely sure how or why. I tried removing some of the visual level filters on the surrounding charts and then undoing the changes. Although this didn't work, changing the top N value to 30 (from 20) and then applying and then setting back to 20 again and applying seemed to work.
I wonder if it some legacy issue when you try to apply Top N filters to visuals originally created in a previous version of Power BI desktop and some new properties just need to be re-applied.
RE: Top n filter
Now that top N and attribute groupings are available, a cool feature would be to allow dynamic group definition based on a value of a measure or attibutes. eg: group top 10 sales products and assign remaining products to others group ...
RE: Top n filter
Thank you for the Top N feature released yesterday, this is an essential feature we have been eagerly awaiting for months now.
I used it within a treemap and it seemed to work perfectly initially. However, when I then start clicking on the items in the treemap, the cross filtering in the other charts around the page return blanks, but only for some items not others. If I increase the N parameter, a larger proportion of the items cross filter correctly.
I am applying Top N on the same dimension name used in the "Group" and top N is applied to the same measure used in the "Values".
I've had a good look around to see if there are any other filters that might be affecting it but can't see anything. And if I remove the Top N filter completely it all starts working fine again, albeit with far too many values in the treemap again.
RE: Top n filter
We should have an ability to filter dataset on the report to show only for example top N records. In our scenario where we want to show top 5 queries sorted by execution time, we are able to prepare a table visualization, which shows all queries and their execution time, but not finding an option to filter top 5 queries based on execution time.
RE: Top n filter
Currently the only way to successfully develop against large data sets is hide them behind views. The within the view you use TOP N to limit the rows during development for speed.
By comparison, Tableau has a feature that allows you to select ALL, TOP N or SAMPLE N when building the columnstore which is very useful.