Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @gdssiqueira
1. Create a new table summarized:
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
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:
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |