Power BI doesn't use decimals

[MaartenH]

Hi,

Been trying all afternoon to get a dashboard together. Problem is that when I upload data with amounts like 2.5 & 3.6, 

the dasboard adds these up in a graph/table etc. as 2 + 3 = 5 instead of 6.1

It's like Power BI doesn't use the decimal numbers...

any ideas? 

[Anonymous]

The steps you can take to make sure your values stay the same as you want them to be in the excel file are: 

Go to   Home > Edit Queries > Edit Queries . Once you are in the Power Query Editor, check the Query Settings on the right side and look at the Applied Steps. You will see the first step being Source, after which you will have Navigation and etc.. If you have data which you have to remove (ex. First 2-3 rows, and then promote the first row -  Use first row as Header, what will happen next is that Power Bi will take Change the type of the columns based on what it finds in the first 200 rows in each column. If you have 0's witout them having decimal numbers in the first 200 rows, then Power Bi will decide that the data type for that column is Whole number. Unfortunately when you change it manually from Whole Number to Decimal Number, that does not always have an effect on the outcome. Therefore what you can do is go to:  Applied steps, find Changed Type and DELETE it from the applied steps. Once this is done go to Transform in Power Query Editor and  Power Bi will no longer Detect the data type on it's own, and it will show Data Type: Any.  When you see Data Type Any, just select the columns you need and change the data type from Any to Decimal points. Click close and Apply and that should do it. 

[jsBrizius]

Maybe the Data Type of the column needs to be Decimal Number.  I created a new column that adds 2.5 and 3.6 and it shows 6.1.  Maybe check your formatting as well?

 

[MaartenH]

There seems to be a problem with the upload: 

this is mu excel, amount has almost 4 decimals: 

tExcel

when uploaded, Power BI just "trims" the amount, and doesn't round it ? 

[MaartenH]

Sorry, it does round it, but the problem is that at a certain point the amounts in the excel are, for example: 

0.02

Power BI rounds that to zero. 

I have around 60 K-rows with amount €0.02 wich is 1.2 k€, for my purpose reporting thats a material difference 🙂 

[sdjensen]

I never liked to use excel as a source for my data. Could you try to save your data excel sheet as a csv file instead and use this as a source and see what you get.

[MaartenH]

I'm new to Power BI, for the moment my data comes from Excel file (looking to link it directly to ERP database/in cloud applications in the future). Would you advise to always save the Excel data in CSV format? 

[KHorseman]

I believe the reason for the csv recommendation is that excel carries a bunch of formatting baggage that can hide little surprises (numbers that come in as text perhaps). A csv file is an unformatted text file. The fewer moving parts in the machine, the fewer places you have to look when there's a problem. Is that about right @sdjensen?

[sdjensen]

---

@KHorseman wrote:

I believe the reason for the csv recommendation is that excel carries a bunch of formatting baggage that can hide little surprises (numbers that come in as text perhaps). A csv file is an unformatted text file. The fewer moving parts in the machine, the fewer places you have to look when there's a problem. Is that about right @sdjensen?

---

@KHorseman - yes that is right. Many years since I stopped using Excel as source in SSIS packages, but I don't know if there is any similar issues with importing from Excel in Power BI, so it was just an suggestion (a long shot) to try to find the problem here.

[MaartenH · Accepted solution]

Never mind, 

In the edit query I found that in the "applied steps" window i made 2 (historic) alterations for the data, first I made it a "whole numer" then a couple steps later a "decimal"....deleted the first step and the data came through ok. 

Solved, -1 kudo for meself!

thanx for the input though!

[basselazzouz]

Thanks for the input as it worked perfectly

[KHorseman]

Have you checked to make sure that the column's data type and formatting match the decimal output you want? Depending on exactly what you're trying to do you may also want to create a measure, even if it's a simple Total Thing = SUM(Table[Thing]) so that you can set the number type, formatting, number of decimal places, etc on the measure itself.