Forum Discussion

gdssiqueira's avatar
gdssiqueira
Helper I
10 years ago
Solved

Subtract values from different rows

Hello,   I'm working on a project and there's something I need your help with. I need to display the difference between two values in different rows. Ideally, I'd need a visual that allowed me to d...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    Hi gdssiqueira

     

    1. Create a new table summarized:

     

    TableWorkSumm =
    SUMMARIZE (
        TableWork;
        TableWork[Date ];
        TableWork[Category ];
        "Result"; IF (
            CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" )
                - CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Refund" )
                = CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" );
            BLANK ();
            CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" )
                - CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Refund" )
        )
    )
     
    2. When you use the Column Result; Select Visual Filter to Result to Is not Blank and Applied
     
     
gdssiqueira's avatar
gdssiqueira
Helper I
10 years ago

I think I jumped the gun on this one. It appears that in my big dataset there are indeed a few occasions where there's more than one 'Refund' row for each Date and Category. I'd be happy with selecting any of the returned Values to do the subtraction, but I couldn't manage to get it working.

Vvelarde's avatar
Vvelarde
Community Champion
10 years ago

Hi gdssiqueira

 

1. Create a new table summarized:

 

TableWorkSumm =
SUMMARIZE (
    TableWork;
    TableWork[Date ];
    TableWork[Category ];
    "Result"; IF (
        CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" )
            - CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Refund" )
            = CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" );
        BLANK ();
        CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Purchase" )
            - CALCULATE ( SUM ( TableWork[Value] ); TableWork[Type] = "Refund" )
    )
)
 
2. When you use the Column Result; Select Visual Filter to Result to Is not Blank and Applied