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.

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).