Power BI and excel do not round decimal numbers in the same manner

[Anonymous]

Hello,

I have a large excel document for which I upload through OneDrive. When I select all columns for an identifier I get a sum of 0 but when I group in power BI to sum the same column I get a balance of 0.04 which is evident through many of the other identifiers. How would I fix power BI to round correctly?

[Anonymous]

the data would be key to see what's going on, but you could transform all the numerical columns to whole numbers.  If you have desire to keep decimal'ed data, then you'd need to transform after loading into PBI.

[Anonymous]

I will attach photos of what I am trying to resolve. The data is confidential, but I take that raw excel data and upload it into Power BI. I group by an identifier(which is shown summed up in excel) and get the different balance in Power BI. I tried to configure as decimal number and fixed decimal number.

Thank you

ExcelPower BI

[Cmcmahan]

We understand that your data is confidential, but would it be possible to provide us with an excel file that has a column (or multiple columns) of unlabeled numbers that demonstrates the issue?  You can change/edit the specific values as much as you like to mask sensitive data, as long as the issue is still clear.

But without having more concrete examples of your issue, we can only guess whether the problem lies in the math engine of PBI/Excel, in the way the data types are transferred, or something else entirely.

[Anonymous]

I would be happy to, How would I attach an excel file to this?

Here is what I would attach. Basically I want to sum B based on A. I do not understand and need to fix Power Bi does not reflect this in the exact same manner.

[vivran22 · Accepted solution]

@Anonymous 

From what I could gather, following may help:

• Go to Edit Query (Right click on the table name > Edit Query) and set the column property of Column A as Text and Column B as Decimal number. Close and Load
• In Power BI Data view, select the column B and under Modelling you can select the decimal places value of Column B

This will help you in getting the sum of Column B by Column A.

Rgds,

Vivek

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

rather than submit a screen capture, can you please just add in the table of cells in your next reply?  This will help us understand the precision vs. type etc. issues you're facing.  Here's an example of copying cells from Excel into your reply: (this way you don't have to upload a file) (just select cells in excel and copy/paste here)

Month Start	Index	ComplaintsPerMonth
1/7/2016	36	2041
1/8/2016	35	1581
1/9/2016	34	1461
1/10/2016	33	3168
1/11/2016	32	2884
1/12/2016	31	2751
1/1/2017	30	3766