cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jburklund
Helper II
Helper II

Setting up data for a matrix table

I have a flat data file for operating expenses that I am trying to turn into a Matrix so the users can download all of the data at once, instead of from 3 different tables (Revenue, Departmental Expenses, and Expenses). 

 

My sample data look like this (grey columns are filtering columns): 

Data.PNG

And I would like the matrix to output like this:

Output.PNG

 

I tried to create another data table that breaks out which variables (Taxes, Insurance, etc.) belong to each category (Revenue, Departmental Expenses, Expenses) and link it to my main data file but the matrix just repeated every value selected under each cateogory.

 

Thank you for your time.

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@jburklund,

 

The first table is raw data, not the table visual, right? You may unpivot the table and create one addtional category column which contains categories like 'Revenue', 'Departmental Expenses' and 'Expenses' then drag the category column and the attribute column into the rows field.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@jburklund,

 

The first table is raw data, not the table visual, right? You may unpivot the table and create one addtional category column which contains categories like 'Revenue', 'Departmental Expenses' and 'Expenses' then drag the category column and the attribute column into the rows field.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the advice. I had to reorganize my data to add category and sub category columns with all of the individual variable values stored in their own row.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.