Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
3 years ago
Solved

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 pres...
Need Help
  • Priya1969's avatar
    Priya1969
    3 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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