Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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. 

 

 

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

       

       

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        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.