Subtract values from different rows

[gdssiqueira]

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 do subtraction but, right now, I what I'm trying to do is create a dynamic column or table with the calculated subtraction.

Here's an example of what my dataset looks like:

Date  Category  Value Type

1/1    Sodas        4         Purchase

1/1    Sodas        3         Refund

2/1    Fruit          7         Purchase

2/1    Meat         2         Refund

2/1    Meat         5         Purchase

What I'd like to do is: have a table, or at least a new column that, for each day that has a purchase and a refund of the same category, calculate Purchase - Refund (there's never more than one row with purchase and one row with refund, for each category, for each day, but there might be less than one like Fruit in 1/1, which has no refunds or purchases, or Fruit in 2/1 which has a purchase but no refunds and doesn't interest me)

So I'd like an ouput like this, for the first example:

Date Category Difference

1/1   Sodas       1

2/1   Meat         3

Or maybe, if using a custom column

 Date Category Value Type Difference

1/1    Sodas      4         Purchase  1

1/1   Sodas        3         Refund    null

2/1   Fruit           7        Purchase  null

2/1   Meat          2        Refund     null

2/1  Meat           5        Purchase   3        

Do you have any suggestions on how to do this?

Thanks a lot!

[ImkeF]

You can use this statement in the query editor:

let
    Source = YourTable,
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Type]), "Type", "Value", List.Min),
    Difference = Table.AddColumn(#"Pivoted Column", "Difference", each [Purchase]-[Refund]),
    AddType = Table.AddColumn(Difference, "Type", each "Purchase"),
    #"Merged Queries" = Table.NestedJoin(Source,{"Caterory", "Date", "Type"},AddType,{"Caterory", "Date", "Type"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Difference"}, {"Difference"})
in
    #"Expanded NewColumn"

As you see in the Pivoted-Column-step we're choosing the minimum value from dups on type. So you could also take the maximum instead if that fits better.

[itayrom]

Relying heavily on-

"(there's never more than one row with purchase and one row with refund, for each category, for each day, but there might be less than one like Fruit in 1/1, which has no refunds or purchases, or Fruit in 2/1 which has a purchase but no refunds and doesn't interest me)",

Adding a calculated column with the following DAX formula should do the trick:

difference = 
    var refund_val = LOOKUPVALUE(table1[value], table1[type], "refund", table1[date], [date], table1[category], [category])
    return IF(AND([type] = "purchase", ISNUMBER(refund_val)), [value] - refund_val, BLANK())

Which basically says "For each row, if 'type' is 'purchase', look up for 'value' where 'type' is 'refund' and 'date' is the current row's date and category is the current row's category. If such a value was found(I.e. a number was returned), return its subtruction from the current row's value. otherwise, return a blank value".

Again, this formula relies heavily on the fact that there is no more than one "refund" counterpart for each "purchase" type, and would fail otherwise.

Also, there are other ways to do this, but this is the most straightforward one I can think of.

[gdssiqueira]

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 · Accepted solution]

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]

That's actually why I added these remarks - it might simplify things a lot and, in my case, is always true.

I will experiment with your solution and come back with feedback. Thanks a lot !