How to switch Columns and Values Headers (Matrix Table)

[muji0317]

Good day!

I've been searching the net and can't find the solution. Can you guys help me how to switch the places of Columns and Values.

I have multiple excel files for different types of reports (sample only: Total Sales Report, Ad Sales Report, Ad Spend Report).

See attached photo.

Thank you!!

Gdrive File Link 

[danextian · Accepted solution]

Hi @muji0317 ,

It seems that you were already in the right direction so I am a bit confused of what you really want to achieve. If you want the metrics to be under the date you can just re-order the fields in column tiles. Please see attached pbix.

Also, I cant open Business Reports (total) file. Error says not in the right format.

[danextian]

Hi @muji0317 ,

The  report is understable to the human eye but the  data needs to be transformed to a format that is Power BI designer friendly. This can be done in Power Query but as always, please post a sample data that is not an an image. The community is here to help but don't make us manually input those data. A link to an Excel file stored in the cloud will do.

[muji0317]

Hello Mr. Danextian.

Here's the link: Google Drive 

If possible, I want to have the Dates under the Main Metrics (Spend, Sales, Orders).

The dates are limited only to 4 columns because our team needs to monitor the current week up to last month that's why we only need 4 columns of Dates. And also it should be in weeks format. Please see photo below.

Thank you!!

[danextian · Accepted solution]

Hi @muji0317 ,

It seems that you were already in the right direction so I am a bit confused of what you really want to achieve. If you want the metrics to be under the date you can just re-order the fields in column tiles. Please see attached pbix.

Also, I cant open Business Reports (total) file. Error says not in the right format.

[muji0317]

Thank you!!

Last question, how can I add a conditional formatting where it will become red when the value is greater/lesser than the previous date? Let's say our sales this week is $100 and last week was $500. The ($100) cell should highlight red.

Edit:
I tried conditional formatting but I think it will not work because we unpivoted the columns of metrics. So the values of a specific metric (Orders, Sales, Spend) cannot be compared to the other date column because they use a single column only (Metrics) inside the table

[danextian · Accepted solution]

For easier time intelligence calculations, it is a good practice to use a separates dates table. You can create one using DAX based on the existing tables in your data model. Please see attached updapted pbix.

[muji0317]

I'm very grateful to you, and to this community. Such a BIG help!

All the best to MS Power BI Team! 🙂