Forum Discussion

duncfair's avatar
duncfair
Icon for Helper IV rankHelper IV
8 years ago
Solved

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 ...
Need Help
  • duncfair's avatar
    duncfair
    8 years ago

    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.

duncfair's avatar
duncfair
Icon for Helper IV rankHelper IV
8 years ago

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.

  • ricardocamargos's avatar
    ricardocamargos
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

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

     

    • duncfair's avatar
      duncfair
      Icon for Helper IV rankHelper IV
      8 years ago

      this site doesn't allow me to upload images.

       

       

      • ricardocamargos's avatar
        ricardocamargos
        Icon for Continued Contributor rankContinued Contributor
        8 years ago

        duncfair,

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      duncfair
      Icon for Helper IV rankHelper IV
      8 years ago

      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.