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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi az38 ,

         

         

        It is almost working but when i select anything in filter then because of firstnonblank function it is not assciating properly.

         

        Thanks