Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

Convert multiple column values to rows

I have 4 columns are Type, Id, Area & result and those 4 columns return multiple values. I would like to group the Type, Id and Area into one table. Currently I am using multiple matrix table to ach...
Need Help
  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    5 years ago

    Well... I created a new calculated column:

     

     Area# = "Area" & RIGHT ( Data[Area], 1 )

     

    and placed it in the column field in the matrix visual.

     

    Then created a new measure and added it to the matrix values:

     

    MyValues = 
IF (
    ISINSCOPE ( Data[ID] ),
    VALUES ( Data[Result] ),
    VALUES ( Data[Area] )
)

     

     

    In the Matrix Row I added both the Type and ID.

     

    In Power BI file options, I disabled the use of modern visual headers. 

     

    And in the matrix visual format, I adjusted the subtotals as follows:

    and made the column headers white (same as background to make  them invisible).

     

    Please check this sample file.

Mohammad_Refaei's avatar
Mohammad_Refaei
Solution Specialist
5 years ago

Hi Saxon10 , can you share an example of the desired output? You can just draft the values in Excel sheet.

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago

Thanks for your reply.

 

In Excel,  Based on my raw data I used two pivot tables and fillter by PR and TTR. 

 

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    I am not aware of any possibility to have more than one layer of column headers in matrix or table visuals (OPS1/OPS2/OPS2 and EPS1/EPS2/EPS3).

     

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    Hi Saxon10 ,

    Actually it's intriguing 🙂

    I managed to create something like this:

     

    Will this help?

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thanks for your reply again.

       

      Yes.This is I want. Can you please advise how do you get the final result? its manual or did you make any formula or measure?

      Could you please share the PBI file and I will try to replicate the same thing at my end. 

      • Mohammad_Refaei's avatar
        Mohammad_Refaei
        Solution Specialist
        5 years ago

        Well... I created a new calculated column:

         

         Area# = "Area" & RIGHT ( Data[Area], 1 )

         

        and placed it in the column field in the matrix visual.

         

        Then created a new measure and added it to the matrix values:

         

        MyValues = 
IF (
    ISINSCOPE ( Data[ID] ),
    VALUES ( Data[Result] ),
    VALUES ( Data[Area] )
)

         

         

        In the Matrix Row I added both the Type and ID.

         

        In Power BI file options, I disabled the use of modern visual headers. 

         

        And in the matrix visual format, I adjusted the subtotals as follows:

        and made the column headers white (same as background to make  them invisible).

         

        Please check this sample file.