Skip to main content
cancel
Showing results for 
Search instead 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

Reply
gdssiqueira
Helper I
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

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
 
TBWSUMM.png
 



Lima - Peru

View solution in original post

5 REPLIES 5
ImkeF
Super User
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]),
    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.

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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

itayrom
Resolver II
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.

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.

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
 
TBWSUMM.png
 



Lima - Peru

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 !

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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