Forum Discussion
Power BI doesn't use decimals
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?
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!
11 Replies
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?
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 ?
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 :-)
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.
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.
