Matt Allington walks us through a complex scenario using the Matrix visual in Power BI:
Not every report you may want to build has such simple layout requirements as the one above. When using an Excel pivot table, there is a feature called “Field, Items and Sets” that makes the process of creating asymmetric pivot tables relatively easy, but that doesn’t exist in Power BI. To help you understand, here is an example of an asymmetric visual (Power BI matrix in this case).
Note in my example above, the first 4 years come from the year column, just like before, but now I have 2 additional columns at the end of the matrix that are not simply the addition of other columns. To create the last 2 columns above I need to write measures, but you can’t have both measures and columns from your model showing like this in a Power BI matrix. Instead you need to build a solution that leverages a separate table to generate the columns you need, and then to generate the results you need in each column.
Below I will show you how to build such an asymmetric matrix like this.
Read on for the solution. This is quite clever but also makes me wonder if the product shouldn’t make this scenario a bit easier for us