cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 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!

1 ACCEPTED SOLUTION
Community Champion

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

Lima - Peru
5 REPLIES 5
Super User

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]),
#"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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Resolver II

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.

Helper I

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.

Community Champion

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

Lima - Peru
Helper I

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 !

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors