Need help with changing matrix structure

[TK202_BI]

Hi guys, I have made a matrix like figure 1. But I'd like to make it structured like figure 2. Can anyone please help me? Thanks in advance!

[v-ljerr-msft]

Hi @TK202_BI,

Based on my experience, I don't think there is an easy to do it in Power BI currently in your scenario. You may need to add a new column which contains two values "INVOICELineAmount", "Invoice Order Count", and create a new measure to calculate INVOICELineAmount and Invoice Order Count.

Could you share a sample pbix file with just some sample/mock data, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[TK202_BI]

Hi @v-ljerr-msft,

Thanks for your reply. Here's the link to my sample pbix file: https://www.dropbox.com/s/vkojuofh7v106vr/Sample.pbix?dl=0. Could you please help me generate the column and measure you mensioned. Thanks!

[v-ljerr-msft]

Hi @TK202_BI,

As "INVOICELineAmount" and "Invoice Order Count" are columns in your table, an easier way is using the Unpivot option in Query Editor to unpivot the two columns into "Amount Type" and "Amount" column. For more details about how to use Unpivot option in Query Editor, you can refer to this article.

Then you can show both "Amount Type" and "Invoice Date" column(only keep Year in the Date Hierarchy) as Columns, and "Amount" column as Values in the Matrix, and click (expand all down one level) on left top of the Matrix to get your expected result. 

Here is the modified pbix file for your reference. 

Regards

[TK202_BI]

Thanks @v-ljerr-msft, it works for the columns in Query Editor. But the Invoice Order Count in my real file is a measure created with DAX. Besides I have another measure Previous Year Invoice Line Amount = CALCULATE(SUM('Sheet1'[INVOICELineAmount]), SAMEPERIODLASTYEAR('dim_date'[Date])) which also need to show in the column in the matrix. I should make those columns same as my real file in the sample file. My apologies. I have updated my sample file here: https://www.dropbox.com/s/um8ivikxnawefai/Sample.pbix?dl=0.

So, the Invoice Order Count and Previous Year Invoice Line Amount don't exist in the Query Editor. Is there any way to make it work for my measures? Sorry for so many questions, I'm really new to Power BI. Thanks for your time.