Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Transpose in visualization alone

Hi All,

 

I would like to have Column header to be displayed in First Column and its values to be displayed in second column.

 

 

We can do transpose in Edit Queries but i want to change my visualization part alone.

 

Thanks in Advance

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ,

     

    Then you need first create a table ,and put all the column headers into one column,such as below:

     

    Then you need a measure as below:

     

     

    Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table (4)'[Header] ) = "Address", SELECTEDVALUE ( 'Table (2)'[Address] ),
    SELECTEDVALUE ( 'Table (4)'[Header] ) = "Amount", SELECTEDVALUE ( 'Table (2)'[Amount] ),
    SELECTEDVALUE ( 'Table (4)'[Header] ) = "Country", SELECTEDVALUE ( 'Table (2)'[Country] ),
    SELECTEDVALUE ( 'Table (4)'[Header] ) = "Gender", SELECTEDVALUE ( 'Table (2)'[Gender] )
)

     

    Finally in the new table ,put column and measure into a matrix as below:

    And you will see:

     

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    In matrix visualization, you have the option Show on Row. That will move you measure on the row.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

       

      Can you explain in more detail on hoe to get that in matrix.

       

      Original dataset

       

      Expected Output

      Thanks

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Anonymous ，

         

        Go to "edit queries">select all columns>"Transform">"Unpivot columns":

         

        Then choose "Use first row as headers":

         

        Choose "close and apply",then you will see :

         

         

        Best Regards,
        Kelly

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

     

    you can use the next technique with creating new calculated table. the exactly solution depends on your data model and aggregation business logic

     

    Table2 = 
UNION(
    ROW("Field"; "Amount"; "Value"; SUM(Table1[Amount]));
    ROW("Field"; "Address"; "Value"; FIRSTNONBLANK('Table1'[Address];1));
    ROW("Field"; "Country"; "Value"; FIRSTNONBLANK('Table1'[Country];1))
)

     

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi az38 ,

       

      I tried your suggestion but i am not getting it properly. Can you explain it in more detaild manner. I assume i am getting confused by the delimiters present in the code which you sent. ( ; , )

       

      Thanks

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        delimiter depends on your system localization settings.

        you can just to replace ";" to "," in my statement

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution