Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
duncfair
Advocate II
Advocate II

Money column displays without trailing zero

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.

1 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.

View solution in original post

7 REPLIES 7
ricardocamargos
Continued Contributor
Continued Contributor

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

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
Not applicable

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).

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.

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

 

this site doesn't allow me to upload images.

 

 

@duncfair,

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors