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.

  • Murtaza_Ghafoor's avatar
    Murtaza_Ghafoor
    Super User
    2 months ago

    nleuck_101 

    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