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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.