Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
loisloriot
Helper I
Helper I

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
Eric_Zhang
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

 

 


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

 

 

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.

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 😞

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

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

@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

 

 

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 !

 

@loisloriot

 

Kindly share any findings you get. Smiley Happy

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.