ROUND & SUM issue

[loisloriot]

Hi,

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])
FROM XXXXX
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 ?

Loïs

EDIT : subject

[Eric_Zhang]

---

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

 

---

 @loisloriot

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.

[ankitpatira]

@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.

[loisloriot]

@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}})

[loisloriot]

Hi,

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.

[loisloriot]

So I focused on 1 day only and voila :

Left SSMS - right Power BI

Not cool, from 50.4 million to 51.2 million 😞

[Eric_Zhang]

@loisloriot

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.

[loisloriot]

Hi @Eric_Zhang, thanks for your help !

you'll find an Excel & CSV file here :

 https://1drv.ms/x/s!Am2pYlkKVneTggbWQe9Xm-tLhuVL

https://1drv.ms/u/s!Am2pYlkKVneTggunFxOEWCO6n6gI

[Eric_Zhang]

@loisloriot

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?

[loisloriot]

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 !

[Eric_Zhang]

@loisloriot

Kindly share any findings you get.