Forum Discussion

Mal_B's avatar
Mal_B
Regular Visitor
9 years ago
Solved

formatting

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

8 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    Cool ,

     

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

     

     

     

     

    let me know if any help .

    • Mal_B's avatar
      Mal_B
      Regular Visitor
      9 years ago

      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's avatar
        Baskar
        Resident Rockstar
        9 years ago

        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.