format data after transpose table

[mypowerbi1]

Hi,

I got the data from source in this format:

I have applied transpose table on this and now data is coming as per the expectation :

I am experiencing trouble to format the individual row's values. Avg Sale value should be in % format, Total sales should have $ before the value, Pending sshould be decimal but all this not possible because these are not column but rows. 

I have tried to set formating in power query 1 step before the transpose table but it got vanished after transpose table step.

Please suggest how to set the formatting for as required above.

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @mypowerbi1 ,

It is not possible to display multiple different formats on the same column. You can use the format() function to convert the table to the form you want after Unpivoting the table, but both are text types.

Here are the steps you can follow：

1. In the power query, except [Group], the rest are selected – Unpivot Columns.

2. Create measure.

Measure =
SWITCH(
    TRUE(),
    MAX('Table2'[Attribute])= "Total sales",FORMAT( MAX('Table2'[Value]),"$#,##0"),
    MAX('Table2'[Attribute])= "Pending",FORMAT(MAX('Table2'[Value]),"Fixed"),
    MAX('Table2'[Attribute])="Avg Sale",FORMAT(MAX('Table2'[Value]),"Percent"),
    FORMAT(MAX('Table2'[Value]),"General Number")
    )

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rajulshah]

Hello @mypowerbi1 ,

This is only possible using Power BI measures and then formatting the measures as you want. We cannot have multiple data types for a single column.

[SanketBhagwat]

Hi @mypowerbi1 
Data type format in Power BI is according to column and not rows.

Please refer the below link;
https://community.powerbi.com/t5/Desktop/Format-on-Row-but-not-columns/m-p/65683


If this post helps, then mark it as "Accept as Solution" and give it a thumbs up.