Skip to main content
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

Helper I
Helper I

ROUND & SUM issue



That's might sound lame but I have a problem doing a simple sum on some of my datas !

At first I added a new column on my table with the following DAX formula :

= CALCULATE (SUM([price])+SUM([tax]))


So if I focus on year 2015, I have 51.2M :





My datas are from a SQL Serv DB, so I checked it on SSMS with the same query used in Power BI just by adding the SUM, removing all others columns and by adding a filter on the year :


SELECT SUM([price])+SUM([tax])
WHERE YEAR(dateCreation) = 2015


But the sum does not match the sum in power BI : 50427520.722284


I tried the same query directly on a new Power BI source, and it's the right value : 50427520,72



I did another test : I took my first query, and made some modification directly on the query editor, yet it's not the correct value : 51203113,61


Do you know what am I missing here ?







EDIT : subject

Microsoft Employee
Microsoft Employee


I did another test : I took my first query, and made some modification directly on the query editor, yet it's not the correct value : 51203113,61




What is the first query and how you did the modification? And what are the data type of price and tax imported in query editor? I doubt the data is not loaded correctly. Try to sort the data by price ascending and copy the data to an excel sheet in query editor. In SSMS select the data of 2015 order by price asc and then copy the data to the same sheet and compare.

Community Champion
Community Champion

@loisloriot I doub't there would be anything wrong in the way power bi sums it. To troubleshoot it you will have to drill down to lower levels. Can you drill down to month's level and check with sql query on your source. The only thing I can think is why you need to use CALCULATE fuction. For your calculated column you can simply create it as, SUM([price])+SUM([tax]). See if that makes any difference.



@ankitpatira : good idea, thanks, I'll try that !


@Eric_Zhang : I took the exact same SQL query and apply those modifications on the query editor :


Added Customer --> = Table.AddColumn(#"Changed Type", "TTC", each [prixnet]+[taxe])

Extracted Year --> = Table.TransformColumns(#"Added Custom",{{"datecreation", Date.Year}})

Removed Columns --> = Table.RemoveColumns( lots of columns here ^^)

Grouped Rows --> = Table.Group(#"Removed Columns", {"datecreation"}, {{"SUM_TTC", each List.Sum([TTC]), type number}})





Just a quick update, I did as @ankitpatira suggest. I found discrepancy for all months. I export all data for January: same number of rows, same value... except Power BI does not bother to stored useless decimal "0" :


Left SSMS export - Right PowerBI export



So I tried to change the data type in PowerBI : from Decimal Number (Auto) to Fixed Decimal Number (6), still the same wrong amount.

So I focused on 1 day only and voila :


Left SSMS - right Power BI



Not cool, from 50.4 million to 51.2 million 😞



Could you export the SSMS output for year 2015 into an excel and share it? I'll try to load it into my database and play it with Power BI desktop.

Hi @Eric_Zhang, thanks for your help !


you'll find an Excel & CSV file here :!Am2pYlkKVneTggbWQe9Xm-tLhuVL!Am2pYlkKVneTggunFxOEWCO6n6gI



The sample data works in my test.  I still doubt the data is not loaded correctly in your case, is there any other way to compare the data in database and in Power BI, what is the table definition(data types for price and tax) in database? By the way, except 2015's, does that measure work for other years?




Hi @Eric_Zhang,



So I guess the issue is me 😞


In DB price and tax are Real (cames from a view) but in the source table those are decimal(19,6). Unfortunatly, that measure doesn't work for any others years.


I'll look in other ways to comapre datas and see if I can change the data type in the view.


Thanks for your help !




Kindly share any findings you get. Smiley Happy

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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


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