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 :).
As Will mentioned, we have started work on new improvements to our table and matrix visuals. If you would like to help us prioritize some of the work we want to do, you can take this quick survey: http://www.instant.ly/s/x5whA Of course we are still reading all the comments on here as well.
Including Will’s last update here as well since everything is still relevant:
Thanks for all the vocal support for this idea! Firstly, some great and exciting news: we’ve begun work on adding more PivotTable-like functionality to our matrix & table. You’ll see conditional highlighting in matrices, selection & cross filtering, expand/collapse and more coming over the next few releases.
This Idea’s got a lot of comments for different things so I’d like to split it up to try and get a better idea of exactly what will help the most people:
- Fredrik’s initial idea was to allow developers using the Custom Visuals API access to the previous levels in a drill-down, to allow for more complex data structures and more complex visuals. If you’re a developer and think we should work on this, please add your votes to: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16608538-custom-viz-access-to-the-previous-levels-in-a-dril
- There are a few comments asking for a dependency tree / breakdown tree visual. It’s been a potential visual for us to build for some time – if you agree that we should build this, please cast your votes here https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6706385-decomposition-tree-visualization-type
- Finally, if you really are looking for more PivotTable type functionality, you can leave your votes where they are. However, please leave any comments about what you specifically love about Excel’s PivotTables and would like us to provide in Power BI. You can also add your votes to these smaller improvements:
Power BI Team
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?
Matt Allington commented
Sorry if this is posted somewhere in the 50+ comments already. They key issue for me is the ability in Pivot tables to move the Values around in the fields list to be where ever you want. E.g. On Rows, columns, nested above or below the column/row heading.
@Dewayne, that's a great request as well - can you please create another idea so we can track it separately? It's hard to parse out all the individual requests from this one massive idea! Thanks!
@Sanjay, you can use Excel against your Power BI datasets already. https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/ You can't put Excel sheets into Power BI reports, but that's a good idea - please go ahead and create another post here so we can track it. Drilling from Power BI reports to somewhere else (another report, another dashboard, another webpage etc.) is on our backlog and you can help prioritize that by voting here: https://ideas.powerbi.com/forums/265200/suggestions/13715889
Sanjay Shah, Pune, India commented
While trying to update the matrix control with more functionality is welcome, but what is really really needed is a Excel Services like functionality in PowerBI working on the same enterprise data gateway. Of course the Excel Services functionality needs to be greatly overhauled but this is really missing. We should be able to author reports in Excel and publish it on PowerBI so that it acts like another page in a PowerBI file. We should also be able to drill down from a PowerBI visual into an Excel Services report. Please work on this asap. The huge grid available in the Excel PT is something the competition does not have and will be a killer feature. If integrated into the pbix file, the user would be able to drill down from a "big picture" using a pbi visual, down to voucher level details in the Excel PT visual.
Dewayne Delk commented
Will, this is very much front of mind for my analysts. The really powerful thing about pivot charts is the multi-field legends that it allows. For instance, if I want to look at commodity demand for a region, country, sector I can drag these to the legend. In PBi, I can only have one on the legend - unless I create a new field. The problem with the latter approach is I don't know in advance where my analysis will take me so it really hinders my ability to quickly analyse large datasets
@marcelo - Yes, that's it!
When you say selection & cross filtering do you mean we will be able to click one row in a matrix or table and filter the data in another visual?
Good news. I was beginning to feel let down by the latest updates and this makes me feel hopeful again.
It's Must Have Visual in Power BI, The game changer visual.
Bryan Swan commented
Decision trees / breakdown trees would be fantastic.
@Guillermo Proano, It 6000 + votes, First idea to cross 6K mark, still Nothing done about it... Hmmmmm
Guillermo Proano commented
5,891 votes should merit a comment - status update on our thinking around this from the PG