Transpose in visualization alone

[Anonymous]

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

[az38]

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]

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]

@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]

Hi @az38 ,

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

Thanks

[amitchandak]

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]

Hi @amitchandak ,

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

Original dataset

Expected Output

Thanks

[v-kelly-msft]

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

[Anonymous]

Hi @v-kelly-msft 

Thanks for your response but do we have any other way to do it without using Query editor and Edit Queries section. i.e., in front end itself or just creating a measure or calculated column?

Thanks

[v-kelly-msft · Accepted solution]

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

[Anonymous]

Hi @v-kelly-msft ,

Let us assume out of these 4 column we have 1 as a measure then how to give that measure value?

Thanks