Matrix Table Visual Breakdown/Drilldown by Column

[newpbiuser01]

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!

[Priya1969 · Accepted solution]

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])+0

4. Then, form the matrix like below, you will get the desire output.

[newpbiuser01]

Thanks @Priya1969! worked like a charm 🙂

[Anonymous]

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

[lbendlin]

Either change  the value display to "Switch values to rows"  or use custom column headers.