formatting

[Mal_B]

How do I get numbers to format in standard accounting formats, that is to say, with brackets around negative numbers and dashes for zeros, justified at the decimal point?   In Excel we use #,###_);[red](#,###);"-   ";@ and  #,###.00_);[red](#,###.00);"-     ";@

What is the equivalent manner of getting this standard formatting in PowerBI

[Baskar]

Hi Dude,

I hope this link will give u more info.pls visit 

https://msdn.microsoft.com/en-us/library/ee634206.aspx

[Baskar]

Cool ,

We can do in DAX Number Formating . Look the below image 

let me know if any help .

[Mal_B]

Thanks for the quick response.  I'm not clear however how I use that without creating new columns as you have done in your example.  Or do I need to duplicate up each imported column with a new formatted column?  

If my query looks like this (it's a bit simplified, but you get the idea): 

let
Source = Table.Combine({Input1, Input2, Input3}),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Date"}, {"Column2", "Reference"}, {"Column3", "Description"}, "Column4", "Amount1"}, {"Column5", "Amount2"}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Amount2] <> null)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Ascending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Date] <> null)),

Sorry, new to this.  I thought formatting things would be easy 😞

in
#"Filtered Rows1"

How do I amend this to get Column4 and Column5 formatted as you suggest?

[Baskar]

No Need to create Calculated column , y not u go with Measure that will help u .

create new measure like this and try,

Measure 4 = FORMAT(SUM('Number Format'[Column4]),"$#,##0.00;($#,##0.00)"

like other measure to . It is in DAX .

[Mal_B]

OK Understood and thanks for that.  However the new column(s), using a measure as you have suggested, comes through as right justfied.   I need it to be decimal left justified, as you get with the Excel (#,##0.00_);(#,##0.00);"-     ";@  format and I can see an option to do that.

Then, ideally, the next bit would be to deal with putting negative numbers into red.   Again I can see ways of conditionally formating cell backgrounds but not the figures themselves and the number in the new measure column appears to being seen as text rather than a figure 

(Surprised it is so difficult to format numbers columns, when it is so easy in Excel).,

Thanks

Malcolm

[v-yuezhe-msft · Accepted solution]

Hi @Mal_B,

We can’t format your columns to be same format as that(#,###_);[red](#,###);"-   ";@ and  #,###.00_);[red](#,###.00);"-     ";@) in Excel.

However, if you want to change the format of amount columns to currency format as other post, you don’t have to create additional column or measures. You can directly change the format under Modeling ribbon.

1. Click your column in Field panel, then change its format to Currency General as shown in the following screenshot.

2. Set Decimal places to 2 when you want the column to show as decimal format.

3. Create a table visual using your amount columns, you will be able to use continual formatting feature to set cell backgrounds.

Thanks,
Lydia Zhang

[Baskar]

Ya i agrre with you @Mal_B.

It bit complex compare with Excel formating.

and It was in string Data type only after format.

coloring text also not possible but we can change in Background color in Table or Matrix report.

[Baskar]

Cool, 

Now u r looking in Power Query. Which i send u that is DAX code.

There is only option u have to create new Calculated column or measure . We can't do with existing column without duplicate.

Sorry my friend.