Format on Row, but not columns

[t-atgeor]

Hi, is there a way to format the data type into different number formatting, based on row, but not columns?

I am having 3 different rows: Total Revenue, Total Cost, and Products (in units), and I would like to  have only the first two in Currency formatting, while the last one into decimal number (no special format).

How do i do so?

Thanks, Atanas

[Greg_Deckler]

Not that I am aware of a way to do that. Maybe consider pivoting/unpivoting your data.

[Seth_C_Bauer]

@t-atgeor What do you have as column names? It almost sounds like you want to pivot the data to make those your columns... You can't have a different data type per row, it is all column based.

[t-atgeor]

Hi @Seth_C_Bauer,

thanks for responding. What I would like to do is the following ( please see picture below). I would like to have the Cost and Revenue in USD, while keeping the products sold as units (but NOT dollars). Currently, I can change the format of the whole column, while am looking only for the two rows change.

Is it possible to have this done?

Regards, Atanas

[Greg_Deckler]

Perhaps something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxRcM4vLlHSUTI00DMwANKmKJShgYGqUqwOTG1QallqXmkqUMIIqtwCqs4ISptC1QcU5ZemJJcoaJTmZZYUawKljA1gJhqA9cMooPpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Current Month" = _t, #"Previous Month" = _t, Difference = _t, #"Difference %" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Current Month", Int64.Type}, {"Previous Month", Int64.Type}, {"Difference", Int64.Type}, {"Difference %", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
in
    #"Promoted Headers"

[t-atgeor]

Hi @Greg_Deckler,

could you please explain to me how I should activate this function. Am not fully aware where to paste it?

Thanks, Atanas

[v-micsh-msft · Accepted solution]

Hi t-atgeor,

As shared by Eno1978 and smoupre, data type in Power BI is column based, changing it by rows is not supported.

We could write measure to calculate the data and then format it to the wanted data type.

An idea about data type in measure, check:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-me...

From your data example, would it be available to have the Total cost, Total Revenue and Product Amount as column, then add another date column to identify the values. In this way it would be available to format the data.

Date	Total Cost	Total Revenue	Product(Units)
2012/01/01	$10.00	$20.00	3000
2012/02/01	$5.00	$8.00	1000
2012/03/01	$15.00	$12.00	3000

Then regarding the difference and the percentage of difference, we could write measure or a calculated column to calculate.

What smoupre did here could be done under Query Editor, when your data is loaded, click Edit Queries, this opens Query Editor, under Transform tab, there are Transponse and Use first row as headers, when done those, on the Home tab, click Advanced Editor, you would find the code which should be similar with what smoupre posted:

Regards