Forum Discussion
sum function ignoring nagetive values
Hello .
I try sum function to find sum of SKU in the following table. In excel pivot table sum function shows BD6-24E = 29 while in power bi sum function show BD6-24E = 30. Can somebody please help me ? I tested calculate and by changing quantity format to decimal. it shows the same result.
regards
Mohsin
Dear mohsin-raza ,
The discrepancy likely arises from how Excel and Power BI handle data aggregation, especially if there are negative or repeated values. Let's break it down:
Values for BD6-24E:
- 24
- 24
- 6
- 12
- -1
Excel Pivot Table Calculation (Showing 29):
If Excel is ignoring the negative value (or potentially applying a filter or a distinct count), it might sum as:
24 + 24 + 6 + 12 = 66 minus the -1 gives 65.
However, if Excel applies a different aggregation rule, it could ignore the repeated 24 or exclude the negative value.Power BI Calculation (Showing 30):
- Power BI's default SUM() should sum all visible rows:
24 + 24 + 6 + 12 + (-1) = 65. If it shows 30, there might be a filter context, data type issue, or incorrect aggregation in the measure.
Troubleshooting Steps:
Check the Pivot Table Settings in Excel:
- Right-click Pivot Table → Field Settings → Summarize Values By → Sum.
- Check if any value filters or calculated fields are applied.
- In Power BI, use a DAX measure like this to ignore negative values:
Total_BD6_24E = CALCULATE( SUM('Table'[Quantity]), 'Table'[SKU] = "BD6-24E", 'Table'[Quantity] >= 0 )Ensure Data Types Match:
- Confirm the quantity column is a numeric field in Power BI.
-
Recheck Data in Power BI:
- Use a table visual to inspect actual row values.
- Use a table visual to inspect actual row values.
Please mark this post as solution if it helps you. Appreciate Kudos.
7 Replies
Thanks alot for your feed back. I refresh the database . Now I get the same answer
Dear mohsin-raza ,
The discrepancy likely arises from how Excel and Power BI handle data aggregation, especially if there are negative or repeated values. Let's break it down:
Values for BD6-24E:
- 24
- 24
- 6
- 12
- -1
Excel Pivot Table Calculation (Showing 29):
If Excel is ignoring the negative value (or potentially applying a filter or a distinct count), it might sum as:
24 + 24 + 6 + 12 = 66 minus the -1 gives 65.
However, if Excel applies a different aggregation rule, it could ignore the repeated 24 or exclude the negative value.Power BI Calculation (Showing 30):
- Power BI's default SUM() should sum all visible rows:
24 + 24 + 6 + 12 + (-1) = 65. If it shows 30, there might be a filter context, data type issue, or incorrect aggregation in the measure.
Troubleshooting Steps:
Check the Pivot Table Settings in Excel:
- Right-click Pivot Table → Field Settings → Summarize Values By → Sum.
- Check if any value filters or calculated fields are applied.
- In Power BI, use a DAX measure like this to ignore negative values:
Total_BD6_24E = CALCULATE( SUM('Table'[Quantity]), 'Table'[SKU] = "BD6-24E", 'Table'[Quantity] >= 0 )Ensure Data Types Match:
- Confirm the quantity column is a numeric field in Power BI.
-
Recheck Data in Power BI:
- Use a table visual to inspect actual row values.
- Use a table visual to inspect actual row values.
Please mark this post as solution if it helps you. Appreciate Kudos.
FarhanJeelani Thanks alot for your detail analysis and solution. Yes there was issue with data format back in database. I refresh the database . it is solved
Please provide example data as text (not a screen print).
With screen print proof of the Exvel formula, DAX formula
Please note you have only highlighted some of BD6-24E rows
Check carefully for lower case and trailing spaces because Power BI is not case sensitive and ignores trailing spaces, where as EXCEL does not.
So in this example Excel would give a sum BD6-24E = 11 +5 = 16
Whereas Power BI = 11 + 5 - 4 - 3 - 2 - 1 = 6 !
BD6-24E = 11
BD6-24E = 5
Bd6-24e = - 4BD6-24E space = -3
BD6-24E space space - 2
BD6-24E space space - 1
Please click thumbs up foerthese suggestions. 👍
also click accept solution if they work.
You can acceot more than one solution. 😀
speedramps Thanks alot for detail analysis and pointing out the data that I missed.
As I mentioned in other replies . I got data format issue which was not measure . I was analysis my results both in power bi and pivot table . I found different result.
After I asked for help. I refresh database . The row with a nagetive value was remove and I got the same result.
speedramps FarhanJeelani powerbidev123 Thanks alot again All of you people for contribution. and every suggestion from you people is the solution to related problems.
Hello mohsin-raza
It appears you may have overlooked some quantities for BD6-24E in your calculations. Based on the screenshot data, the total quantity for BD6-24E is 65, not 30 or 29. This includes all instances of BD6-24E, even the negative quantity. Here is the screenshot of your data where I have highlighted the rows that you have missed in light blue color:
Power BI also confirms this total of 65,i.e., it is also taking care of the negative values in the dataset. Here is the screenshot:
Could you clarify what might be causing the discrepancy?
Could you please provide sample data that fully represents your issue or question in a usable format, rather than a screenshot? Ensure the dataset is anonymized and does not contain any sensitive or unrelated information.
Additionally, it would be very helpful if you could share the expected outcome based on the provided data - this can be in any format, including a screenshot. Having a clear reference for comparison will significantly improve the chances of getting the correct solution in the first response.
For best practices, you may find the following links useful:
Thanks,
Udit
