Create a Matrix Table with multiple fields as rows

[Anonymous]

Hi,

I'm trying to re-create the below excel table in Power BI.  I believe it needs to be built as a matrix table but I'm struggling to get it built.

The table in excel has Employee, Member, Total Charges, Discount Amount, Employee Responsibility, Exclusions, Other Insuranance, Plan Payment and a Date field as columns.

However, I cannot get the table to be formatted with these fields as rows.  I'd like to use the Date column as the column and each field as a row.  Is there a combination of format settings I need to use?

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[PaulDBrown · Accepted solution]

@Anonymous 

By the looks of your source table, you need to make the rows into columns and columns into rows to get this table:

Once you have your data with this structure you can create measures and the visual you are looking for.

To tranform you source table, follow these instructions:

1) Select you "Paid" column and under the Transform tab, choose "unpivot other columns":

2) now select the "paid" column and choose pivot column with the options shown below:

Now you have the structure you need. Create the measures for each field (column) and create a matrix with your month in the column bucket and the measures in the values bucket. 

Go into the formatting pane, and under values, choose "Show on Rows":

And you will get this result:

[Anonymous]

One problem I'm running into when unpivoting is that not all fields (rows) once unpivoted are not the same data types.  Some are dollar types while others are Member Numbers.  So, if I want to list Total Paid Dollars but also Count the number of Members...how do you do that when unpivoting?

Is that possible?

[parry2k]

@Anonymous you need to unpivot your data to achieve this, select date column in power query, right click, and choose unpivot other columns, you will get two column attribute and value, attribute will be your column header,

now in matrix, you can attribute on rows, date on columns, and value column on values section.

[Anonymous]

I have tested on my side, but not reproduce the issue.

Could you try to update the latest version powerbi desktop and try it again? If possible, could you please share this sample pbix file for us have a test?

Of course, recreate this pbix file is a good way that you could try.

here are some simple way you could refer to:

Step1:

Open power bi desktop, the copy all the query in advanced editor of old pbix file and reuse in new report as below:

Note: the query name is same with old pbix.

Step2:

If you had created calculate measure of column in old pbix, just copy the formula and recreate them.

Step3:

Now you could copy and paste visuals from old report to new report.

I hope these steps on helps you with your Power BI service.

Regards,

Lewis