Forum Discussion
Matrix Table Visual Breakdown/Drilldown by Column
Hello,
I have a question about splitting data in a matrix table visual. My data table contains spend and expense data for different managers across different years.
I am trying to present it in a matrix plot shows the sales and expense numbers broken down by year as follows:
| Project Manager | Sales | Expense | ||||
| 2020 | 2021 | 2022 | 2020 | 2021 | 2022 | |
| Bob | 100 | 0 | 100 | 200 | 0 | 100 |
| Mike | 0 | 0 | 40 | 100 | 0 | 0 |
However, when I try to do this in Power BI, I can't present the spend and expense numbers for different years side by side. I tried putting the Year field in the Columns field of the matrix visual and so it just shows year on the top and broken down to show spend and expense, whereas I need spend broken down by year and then expense broken down by year. This is what I get in Power BI currently:
I'd really appreciate any help!
1. Go to Power Query Editor, select expense and sales column, unpivot those columns.
2. Create a new table for replace the blank space in the Matrix.
*Home - select create newtable. Enter column name Project Mgr and their name.3.Once you created a table, create a measure in the new table, using this formula
Measure = SUM(Sheet8[Value])+04. Then, form the matrix like below, you will get the desire output.
4 Replies
1. Go to Power Query Editor, select expense and sales column, unpivot those columns.
2. Create a new table for replace the blank space in the Matrix.
*Home - select create newtable. Enter column name Project Mgr and their name.3.Once you created a table, create a measure in the new table, using this formula
Measure = SUM(Sheet8[Value])+04. Then, form the matrix like below, you will get the desire output.
Thanks Priya1969! worked like a charm 🙂
Either change the value display to "Switch values to rows" or use custom column headers.
HI newpbiuser01,
You can do unpivot column on sale and expense fields to convert them to ‘attribute’ and ‘value’, then you can use the ‘attribute’ on column field above the year and put the ‘value’ to matrix value field.
Unpivot columns - Power Query | Microsoft Learn
After these steps, these records will be group by 'project manager', 'attribute' type and 'year'.
Regards,
Xiaoxin Sheng
