Pivot tables and breakdown trees
The current version of Power BI visualsation framework does not support access to the previous levels in a drill-down. I.e. you only get access to the current level when rendering. That makes it very hard to develop visualizations such as pivot tables or breakdown trees. Make this possible and I would gladly submit a stable version of my Breakdown Trees :).
We released a preview of a new matrix visual with today Power BI Desktop release. This new matrix can be used as a source of cross-highlighting, has a stepped layout, can be drilled in to /out of hierarchies, and has performance updates.
You can read all about it in our blog post: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-feature-summary/#matrix
During the preview we will be adding even more new features, so keep watch each month for more improvements and let us know in comments what else you’d like to see.
While the stepped layout was big on the wish list, it seems that by using the Matrix Preview I've lost the ability to manually adjust those column widths. Since I had a MTD dashboard which summarizes daily quantity and duration, and default column headings are quite lengthy once you tack "Count of" or "Maximum of" prefix to the field. It leaves a view of only 4-5 days without scrolling to see the full MTD (not a desirable quality in a dashboard, BTW). Wish I had my adjustable column width back -OR- that it was possible to override the column header (with renaming the field, since it's used elsewhere and "#" would not be descriptive or accurate). Unfortunately, I'm back to the old Matrix with filtered rows until this is resolved.
Amazing! Could be better with possibility to change text dimension like original matrix
Mark Walter commented
I LOVE the new matrix preview, a really big step for sure. Wondering if it will be possible to cross filter AND drill in on a second table? For example, I have a sales table on the left that allows me to filter a select customers sales. The table to the right does cross filters to show that customers sub products purchased. I now want to drill in to that second tables selected sub products and see the parts purchased by my selected customer. When I do so, the original customer table seems to loose the filter? Just a thought, i'm sure it's in the mix. Also, being able to determine what gets a sub total and what doesn't would be awesome! Thanks again for this awesome feature! We're already using it in a much asked for drill in report!
New matrix looks great, but removing all of the column totals is a deal-breaker. So close, but back to waiting another month (or more).
Great March 2017 updates, specifically the new matrix, getting closer to getting the decomposition tree ......
Demétrio Silva commented
Same here, great news which I have been waiting for for some time.....
great to hear that - i have been waiting for this improvment. Power BI is getting better and better.
Shintaro TAKEMURA commented
Sorry for my typo. Please support hierarchical column headers like old matrix for new matrix .
Alternatively, the "Export Data" option of the Matrix visual could include in the Excel document a pivot table which matches the Matrix visual, in addition to the underlying data table which it already provides. Then the user could double-click the pivot table cell to get a filtered list.
For pivottable-like functionality, I'd like to be able to right-click or double-click (in Excel it's double-click) on a cell in the matrix and see the underlying data, or export that data to Excel similar to the "Export Data" option found when clicking the ellipsis in the top-right corner of a visual.
Kyle Mitchell commented
Please add pivot like expand & collapse buttons. I have seen the latest word wrap feature however the ability to word wrap all row headers & column headers (or values headers) would be awesome!
This is a really great tool so far & obviously some further tweaking will make this immense. I have used other reporting platforms such as Qlikview & SAP Lumira however I think Power BI is a front runner purely down to current ability, potential & cost. Thanks!
Please add a option to format completely the column header. Justify, centralize, bold, italic... all those format option that can make a prettier dashboard.
Greg Turner commented
Sorry if it's already been commented previously, but I would like the ability to choose which individual columns I actually want to display sub-total on in a matrix, and not be give a giant heirarchy of subtotal for every single column displayed making the data very unfriendly to read if many static data columns need to be displayed. It would also be of great benefit to have a scrollbar for the static data when many columns are required, and not just on the totals section which usually requires less real estate than the related static information. Loving Power BI and excited about seeing that other requested improvements to this area are being addressed :)
Ivan Tamburic commented
We also need drill-down to detailed data from an aggregated report...
William McLellan commented
Please allow subtotals to be named appropriately instead of just "total," and allow grand totals to be called "Grand Total." As others have commented, this entire update of matrices to look and work like pivot tables is a major roadblock to acceptance by finance departments!
Mark Walter commented
I work with pivots as a financial analyst, for me the most used/important features are: 1. Be able to display Classic View with sub totals under my values. 2. Be able to format text and highlighting for all sub totals or columns. 3. The ability to expand and collapse all. 4. Sorting each level. 5. Filter a row based on a value condition. 6. The ability to custom name my columns (not being forced to view a SUM preceding my column name.
Thats all i can think of now, please note that picking my default as classic is a big deal for me and others in excel. Bill Jelen included this in an excel idea that has been started.
Thanks for listening and all your hard work on this AMAZING product!
Please consider adding Sorting Options in the Matrix as those available for the Pivot Table which allows for sorting at EACH level.
For example in Excel if you have this Pivot Table setup:
Rows: Year, Type, Company and Location
Values: Total Revenue (Measure)
In a Pivot Table you can sort at each level!
So you can sort your Year descending by Year, Type ascending by Type, Company also ascending by Company and lastly Location descending by Total Revenue. So in Excel you will always be looking at the current Year (on top), followed by Type and Company (A to Z) and only your Locations will be sorted based on Total Revenue best to worst. And by the way all previous years are collapsed only showing annual totals but you can always open a certain year and find what you are looking for. Now if you want to you can go and sort at the Company level but this time descending by Total Revenue (instead of by Company A to Z). Again Year and Type will not be disturbed you will get your current Year (on top) followed by Type (A to Z) and then the Companies will be sorted best to worst based on Total Revenue and the Locations for each Company will also be sorted best to worst based on Total Revenue. None of this is possible in the Matrix! Thanks!
@Amanda Great Pivot table is and must have feature in Power BI, however what about a Decision trees / breakdown trees would be fantastic to use and represent dependency data. Power BI team should have there own Dependence visual by now. Thank you!
Ronny Hagen commented
Any indication on the release for this (or parts of this) idea? Are we aiming for this year or next?