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 ,

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.

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thank you so much for your help. Much appreciated. I will check and update the feedback to you. 

      • Mohammad_Refaei's avatar
        Mohammad_Refaei
        Solution Specialist
        5 years ago

        Most welcome... Good luck

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Hi. Thanks you again and this is  brilliant solution. You provide new solutions for more than one layer of column headers in matrix visual.

       

      Can you please advise one more thing here, if I changed the settings based on the your advice it don't affect exiting visual reports? 

      • Mohammad_Refaei's avatar
        Mohammad_Refaei
        Solution Specialist
        5 years ago

        It is independent from other visuals as we calculated a custom value specifically for this visual (MyValues measure)

         

        #update: disabling modern visual headers will affect all your visuals.