Forum Discussion
Negative values not be summed correctly
Hello All,
I have some data that has negative values but it doesn't seem to be adding up correctly. In the Report view, it's not showing one of the values as negative but in the Table view it has the minus sign in front of the value. The column is a data type Whole Number with a format of Whole Number. How can I make sure Power BI is adding the values correctly? Any help would be greatly appreciated!
We ended finding the issue. There was a relationship with another table with incomplete data. This was filtering out some of my data. Once it was fixed the math started to add up correctly.
9 Replies
Hi nleuck_101,
I hope you are doing well today ☺️❤️
So at first you should know that the format Whole Number does not control the math its just only controls how the value is displayed that means:
If your total should be 7 but Power BI shows 9 so you need to check these first:
Make sure the field is truly numeric in Power Query or Data view tab
Verify it is not some how converted with something like ABS() or any custom column
- In the visual use an explicit measure like the one provided by Parchitect
- Put ItemQuantity in a Table visual and set it to Do not summarize to confirm the raw values
So the most likely issue is that the report visual is not using the raw negative value correctly or the value is being changed before aggregation
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.We ended finding the issue. There was a relationship with another table with incomplete data. This was filtering out some of my data. Once it was fixed the math started to add up correctly.
nleuck_101
First of all you need to check if you are using any measure like
Amount=ABS(Table(Amount))This would remove the -ve sign, so you need to check if there is any DAX measure is applied here.
2nd step: Check your manual formatting like if some custom formatting applied in here. which is
" #.##0.- #.##0 "
This would display negative numbers without -ve sign.
Hope this will solve the issue.If this helps, ✓ Mark as Kudos | Help Others
- Hi nleuck_101,Power BI should sum negative whole numbers correctly by default.I would first check whether the issue is calculation or display/formatting.Create a simple measure:
Raw Total = SUM ( 'Table'[ItemQuantity] )Put this in a Card visual with no filters. If your data has one -1 and nine 1s, the result should be 8. Parchitect and Murtaza_Ghafoor
My measure is SUM(Table[ItemQuantity])- Its hard knowing exact root of cause without having access to the data, but we can try ti create these debug measures:
Row Count = COUNTROWS ( 'Table' )
Negative Rows = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ItemQuantity] < 0 )Positive Quantity Sum = CALCULATE ( SUM ( 'Table'[ItemQuantity] ), 'Table'[ItemQuantity] > 0 )Total Item Quantity = SUM ( 'Table'[ItemQuantity] )
Put these measures in card visuals with the same filters as your table visual.
Expected result should be:
Row Count = 10 Negative Rows = 1 Positive Quantity Sum = 9 Total Item Quantity = 8
If Total Item Quantity still returns 9, then either the -1 row is not included in the visual context, or there are more rows/values being included than expected.Parchitect
When I use your measures I get 9 total rows (expected), Blank for negative rows (expect 1), and 9 Total Items Sold (expect 8).
The picture you attached shows 10 rows, so the total appears to be correct.
The reason you are not seeing the -1 value is that the column is currently being grouped. As a result, the individual -1 value is not displayed.
If you change the field formatting and restrict the Automatic Sum option, as shown in the screenshot, you should be able to see the -1 value correctly unless there is any filter context in place.
Please let me know if you need any further assistance.
If this helps, ✓ Mark as Kudos | Help Others
Proud to be a Fabirc SuperUser
