Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
muji0317
New Member

How to switch Columns and Values Headers (Matrix Table)

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 

 

 

Annotation 2023-06-20 092117.png

2 ACCEPTED SOLUTIONS

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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!!

 

muji0317_0-1687229146714.png

 

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.

muji0317_0-1687232698444.png


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

muji0317_0-1687233508272.pngmuji0317_1-1687233582144.png

 

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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

All the best to MS Power BI Team! 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.