topic Re: sum function ignoring nagetive values in DAX Commands and Tips

sum function ignoring nagetive values

mohsin-raza — Mon, 17 Feb 2025 10:19:31 GMT

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

Re: sum function ignoring nagetive values

powerbidev123 — Mon, 17 Feb 2025 10:58:38 GMT

hi mohsin-raza , Can you share your DAX.

I am getting the expected value i.e 29

Re: sum function ignoring nagetive values

FarhanJeelani — Mon, 17 Feb 2025 11:05:03 GMT

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.

Please mark this post as solution if it helps you. Appreciate Kudos.

Re: sum function ignoring nagetive values

mohsin-raza — Mon, 17 Feb 2025 11:05:05 GMT

powerbidev123

Thanks alot for your feed back. I refresh the database . Now I get the same answer

Re: sum function ignoring nagetive values

speedramps — Mon, 17 Feb 2025 11:06:41 GMT

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 = - 4

BD6-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. 😀

Re: sum function ignoring nagetive values

mohsin-raza — Mon, 17 Feb 2025 11:09:04 GMT

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

Re: sum function ignoring nagetive values

mohsin-raza — Mon, 17 Feb 2025 11:21:57 GMT

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.

Re: sum function ignoring nagetive values

quantumudit — Mon, 17 Feb 2025 11:30:34 GMT

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