cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
loisloriot Regular Visitor
Regular Visitor

ROUND & SUM issue

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

10 REPLIES 10
ankitpatira Super Contributor
Super Contributor

Re: SUM issue

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

 

 

Moderator Eric_Zhang
Moderator

Re: SUM issue


 

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.

loisloriot Regular Visitor
Regular Visitor

Re: SUM issue

@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 Regular Visitor
Regular Visitor

Re: SUM issue

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

Capture1

 

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 Regular Visitor
Regular Visitor

Re: SUM issue

So I focused on 1 day only and voila :

 

Left SSMS - right Power BI

Capture2.PNG

 

Not cool, from 50.4 million to 51.2 million :-(

Moderator Eric_Zhang
Moderator

Re: SUM issue

@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 Regular Visitor
Regular Visitor

Re: SUM issue

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

Moderator Eric_Zhang
Moderator

Re: SUM issue

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

Capture.PNG

 

 

loisloriot Regular Visitor
Regular Visitor

Re: SUM issue

Hi @Eric_Zhang,

 

 

So I guess the issue is me Smiley Sad

 

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 !

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 315 members 3,186 guests
Please welcome our newest community members: