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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Unpivot table not in PowerQuery but in the matrix visual

Hello everyone, 

 

Thanks for taking the time to check this question.

 

I have a huge .pbix which has quite a few tables. The issue with these tables is that they have a lot of columns. Some times they have up to 200 columns. Now, these columns are "calculated measures" which includes divisions and things like that.

 

What I need is to go and have those x200 columns going into rows.

 

I know that the typical solution is to go to PowerQuery and to Unpivot the database. However, this doesn't work for me as I have all these calculated measures.

 

 

Visual Exmaple:

So this is an internet picture just to show you how my vertical line would look like. And as I say... most of those rows come from columns that are calculated measures. 

IncomeStatement_Example

 

 

 

ps: I have use R to create a R visual table, but it is not very visual. The aesthetics are very limited.

 

 

Thanks a lot for your help,

 

Best regards

Facundo

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello Felix!

 

Thanks a lot... and I apologize for the delay. I had crazy working days.

 

During the weekend I found a dififerent solution for this specific issue: to use the graphic visual called financial reprting by profit base. It actually does exactly what I need.

 

However, this is a solution for a specific problem, I am really interested in the solution you mentioned.

 

@MFelix  you asked me about the columns. Actually we have hundreds of metrics in the hospitalitity. Sometimes I need those hundreds of metrics in columns but also separated by "CURRENT YEAR", "LAST YEAR", "BUDGET", and all the comparisons... There are times where I even have to split that into months and even into weeks. There are times where I have to go to 20 years back in time. 

 

Best Regards

FAcundo

View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @Anonymous ,

 

There is an option on the matrix visualization that is show on rows, if you turn that on your measures/columns will be placed on rows insted of the columns.

 

Be aware that the format (text sizes, colors and so on) has you have on your screenshot will probably not be achievable.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks a lot Felix, 

 

Actually I realized that you are right, I never found that button before.

 

However... another issue has come up. With that solution, I only get one column, and even though that may work for some scenarios, what I actually need is to have multiple columns. Let's say

 

Column 1: Current Year

Column 2: Last Year

Column 3: %Increase from CY vs LY

 

Best Regards

Facundo

 

 

Hi @Anonymous ,

 

My answer was given taken into account the image you presented based on that need that you have you need to change the way this is getting done.

 

Just to clarify you have a measure for each line (revenue, profit,...)in your matrix and one for eac column (CY, LY, CY vsLY)

 

If this is the case you need to create two unrelated tables:

  • One table with all the names of measure you are using on the rows
  • One table wit CY, LY, and CY vs LY
  • Then you need to create a SWITCH statment to make the selection of the measures something similar to:
Selection_Measure =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Table[LineHeader] ) = "Revenue"
        && SELECTEDVALUE ( Table[ColumnHeader] ) = "CY"; [Revenue_CY_Measure];
    SELECTEDVALUE ( Table[LineHeader] ) = "Revenue"
        && SELECTEDVALUE ( Table[ColumnHeader] ) = "PY"; [Revenue_PY_Measure];
    SELECTEDVALUE ( Table[LineHeader] ) = "Revenue"
        && SELECTEDVALUE ( Table[ColumnHeader] ) = "CY vs LY"; [Revenue_CY_Measure] - [Revenue_PY_Measure];
...
...
...
...
)
  • Now use the two tables has rows and colum headers and this measure as your values in the matrix.

Be aware that using this type of measure since you have % and € values you need to use also the format to give you the correct formatting.

 

This is just a generic answer if you have any specific with your data I need a little bit more context to help you.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks a lot Felix!!

 

Sounds good to me but infortunatelly I have never used the tools you mentioned. 

 

I have created a .pbix with the situation as I think it will be easier to understand.  There I created:

  • A source table, with the raw data
  • A table where I created a couple of quick measures
  • Then a couplle of visual:
    • How it looks like with the "show on row"
    • How it should look like...

 

I have no words to thank your help and support. I struggle during months with this issue. I thought it wasn't possible to do...

 

Here a link to Dropbox where I uploaded the .pbix

DROPBOX PBIX 

 

Thank you!!

 

hI @Anonymous ,

 

Looking at you data believe that the best option is to make a more profund change to your data.

 

I will work on another approach and send it you, just tell me one thing is your data always with columns rooms/revenue per year ?

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hello Felix!

 

Thanks a lot... and I apologize for the delay. I had crazy working days.

 

During the weekend I found a dififerent solution for this specific issue: to use the graphic visual called financial reprting by profit base. It actually does exactly what I need.

 

However, this is a solution for a specific problem, I am really interested in the solution you mentioned.

 

@MFelix  you asked me about the columns. Actually we have hundreds of metrics in the hospitalitity. Sometimes I need those hundreds of metrics in columns but also separated by "CURRENT YEAR", "LAST YEAR", "BUDGET", and all the comparisons... There are times where I even have to split that into months and even into weeks. There are times where I have to go to 20 years back in time. 

 

Best Regards

FAcundo

Greg_Deckler
Super User
Super User

Can you post sample sample data? Not all 200 columns obviously. By measures, do you mean measures or calculated columns? Just checking.

 

Maybe DAX Unpivot? https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg!

 

Wow, thank you for the quick reply!

 

Sorry, I meant "Quick Measures". Most columns are created using quick measures as they are divisions or increment percentages and stuff like that. Some of them are measures which include complicated equations. None of them can be done as a calculated column because when you use an aggregation powerbi will sum up the results. 

 

I can't release the .pbix as this is a company dataset with highly sensitive data. I hope that the explanation above is sufficient.

 

 

note:

In case it helps. My dataset is related to a hotel company which has several hotels. I use "measures" because sometimes you need to see the data for one hotel, or maybe a combination of hotels. That combination of hotels may vary according to the need of the dashboard user. At the end of the day, we have an endless number of combinations. This is the reason why I am pushed to use "measures" instead of calculated columns. However, once I have all my measures done, I can only use them as columns and not as rows.

 

Best Regards,

Facundo

 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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