The Power BI Matrix visual is similar to an Excel Pivot table, however there are quite a few limitations in the Matrix. In the Power BI Matrix below I have put the product categories on Rows, Calendar Year on Columns and the measure [Total Sales] on Values. At this point, it looks pretty much the same as an Excel Pivot table. It is possible to add more measures to the same matrix, ie [Total Sales PY] as shown below. The default behaviour for a Power BI Matrix is that the column (Year in this case) is on top followed by the 2 measures nested below.
For contrast, let me show the same scenario with an Excel Pivot table. There is an option to drag the Values to Rows so the measures are stacked beneath the rows as an alternate presentation if desired. This actually can be done in the standard Power BI matrix, too.
However, in a Pivot Table there is also a third option to drag the Values above the Year in Columns in effect nesting the years below the measures instead of the measures below the years.
This third alternative is not possible in the standard Power BI matrix
Inforiver Custom Matrix to the Rescue
I have been fortunate to work with Lumel, the company that has developed the Inforiver custom visual. In that capacity, I get to see what they are building and sometimes even influence the features. The Inforiver custom matrix visual has many more features to visualise data than the standard matrix visual including the ability to nest measures this way.
Below is the standard display – the same as the standard matrix.
The same two alternatives provided in Excel are available in Inforiver. Go to Layout\Measures\ and select either “in Rows” or “in Columns”.
In rows looks like this.
And in columns looks like this.
With Inforiver custom visual it is also possible to hide some of the columns in the display. Just right click the columns you don’t want to see, select “column display”, then “hide column”.
More information available at https://inforiver.com/
Thanks for the tip, Matt. As a small org, it’s hard for me to convince them get subs for these tools, I’d have to be using it very often. But I will keep it in mind.
Great Stuff. So unfortunate we need to use a custom visual to use features that are native to Excel. Aren’t Power BI and Excel developed by the same company?
I predict that when Power BI is the same age as Excel is today (ie in 30 years from now), Power BI will have all the features that Excel has developed over its 37 years of existence.
So what do we use in the meanwhile?
I liked the video.
I am well aware of the fact that Excel Power Pivot and Pivot Table, is a far more flexible tool to Bild Table Reports than Power BI.
I did not follow lately on the improvements in table reporting in Power BI and I am pleased to learn someone (Inforiver) is helping close the gap.
I can think of some more flexible features in Pivot table I’d be glad to know if can be matched in Power Bi With or without Inforiver:
1. Subtotals can be on the top or bottom
2. Levels can be compress in one column (as in this example) or spread upon several columns (and can in also in the same report have some levels compress and other separated).
3. Some levels can not be subtotals as other can.
4. If, (a bit like in your example), there are more than one dimension and measures, in rows or columns, then the measures can be anywhere in order first, last or in the middle.
5. One can neatly put 2 or more pivot tables one above the other and make them very easy look like one complexed report.
6. With cube Value you can build any static unstructured report, from date in your model, one can imagen.
7. A feature you and most customers do not need but is critical in some Languages: to have the table change to start from right to left.
8. And probably more…
Are some of these features available in Power BI with or without INFORIVER?
You are right @Uriel Ilan, but I wanted to add something more to it.
Comparison of Power BI, using Power Pivot in Excel isn’t as intuitive and simple. Of course, once you get a hang of it, it will be easier for you. But against Power BI’s interface, Excel’s Power Pivot falls a little bit flat. In Power BI, changing from reports to dashboards to datasets is somehow easy.