Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

Need Help

10 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

     

    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

     

     

     Anonymous

    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.