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 #,###_);[r...
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

Baskar's avatar
Baskar
Resident Rockstar
9 years ago

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's avatar
Mal_B
Regular Visitor
9 years ago

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

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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