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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 or follow my page on Facebook @DAXJutsuPBI.

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 or follow my page on Facebook @DAXJutsuPBI.

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 or follow my page on Facebook @DAXJutsuPBI.

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 or follow my page on Facebook @DAXJutsuPBI.

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 or follow my page on Facebook @DAXJutsuPBI.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors