Forum Discussion

TK202_BI's avatar
TK202_BI
Frequent Visitor
8 years ago

Need help with changing matrix structure

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!      
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

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. :smileyhappy:

 

Regards

  • TK202_BI's avatar
    TK202_BI
    Frequent Visitor
    8 years ago

    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.