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

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

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

     

    Regards

      • 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