Unpivot table not in PowerQuery but in the matrix visual

[Anonymous]

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. 

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

[MFelix]

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.

[Anonymous]

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

[MFelix]

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.

[Anonymous]

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

[MFelix]

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 ?

[Anonymous · Accepted solution]

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]

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

[Anonymous]

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