Forum Discussion

nleuck_101's avatar
nleuck_101
Continued Contributor
2 months ago
Solved

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...
Need Help
  • nleuck_101's avatar
    nleuck_101
    2 months ago

    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.

Parchitect's avatar
Parchitect
Solution Sage
2 months ago
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.
nleuck_101's avatar
nleuck_101
Continued Contributor
2 months ago

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

 

  • Parchitect's avatar
    Parchitect
    Solution Sage
    2 months ago

    You should get 10 total rows. You did get 9. This confirms the issue.

    From your picture in the first post:

    Based on your screenshot, the data has 10 rows total, thats why 9 is not correct:

    -1
1
1
1
1
1
1
1
1
1


    So the expected total SUM itemquantity is:

    9 - 1 = 8

    But your debug cards show:

    Total Rows = 9
Negative Rows = Blank
Total Item Quantity Sold = 9

    That means the measure is not evaluating the same 10 rows shown in your data view. The -1row is not included in the current filter context, or the measure/visual is using a different table or column.

     

    Please check that the table and column used in the measure are exactly the same table and column where you see the -1

    Also create this test measure:

    Negative Rows All =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ItemQuantity] < 0
    )
)

    If this returns 1. then the negative row exists but is being filtered out by visual/page/report filters or relationships.

    If this still returns blank, then the -1 row is not in the same table/column used by your measure.

     

    I would also add a unique Index column to the table visual together with ItemQuantity, and set ItemQuantity to Don’t summarize. That will confirm whether the -1 row is actually present in the report visual.