Money column displays without trailing zero

[duncfair]

I have a report that displays values from a SQL Server money data type column. These columns display in Power BI without trailing zeros and it looks terrible.

Impact

$8001.61

$8500.3

$7500.25

etc.

[ricardocamargos]

Hi @duncfair,

Are you formating the column / measure as currency ??

Check it out:

https://community.powerbi.com/t5/Desktop/Formatting-measure-values-as-currency/m-p/17254

Ricardo

[duncfair]

No.

The column has a money datatype in the Source (SQL Server) and keeps trailing zeros when queried from there or viewed with SSMS, etc.

When presented in Power BI, however, the column has trailing zeros dropped - even though Power BI displays a $dollar sign.

[Anonymous]

Based on your replies it sounds like you haven't formatted it as a currency within PowerBI.  Even though it formatted in SQL, that doesn't mean the formatting will be completely maintained within Power BI.  Click on the field and go to the Modelling tab.  Check that the Data Type and Format is correct.

Another area worth considering is that certain visuals also apply their own formatting, such as showing values as 24K rather than 24,000.  There are settings related to those aspects and you can also specify decimal places.  In your case you might spefity the 'Display Units' to be "None" with the 'Value Decimal Places' to be "2".  Often you find these properties under 'Data Label' or a similar category in the formatting part of the visualization pane (looks like a paint roller).

[duncfair · Accepted solution]

Everything was correct, but Power BI was - for some reason that I would really like explained to me - using the default 'auto' format option (beneath the datatype) of NOT using two zeros for displaying currency.

So the problem wasn't a datatype problem, but a problem with Power BI defaulting to a 'faulty' format option for currency.

[ricardocamargos]

Can you print an image of this in Power BI, also the image of a table struture/sample data ?

[duncfair]

this site doesn't allow me to upload images.

[ricardocamargos]

@duncfair,

You can use any online storage to upload it (dropbox for example).