sum function ignoring nagetive values

hi mohsin-raza , Can you share your DAX.

I am getting the expected value i.e 29

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.