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.
I got a sales table with different types of sales.
I got a “reservation” and the “actual” sales.
Table looks like this:
Customer, OrderID, Sales, Sales Type, Item
A, 123, 100, “Reservation”, D
A, 123, 25, “Sales”, D
A, 234, 43, “Sales”, D
Customer can make a reservation amount which they can buy.
Sales can be a part of a reservation, but can also be a non related sale.
The relation is in the OrderID. If there is a “Reservation” with the same orderID as sales, then the sales is part of the “reservation”.
I want to mark the second row as part of a “Reservation”. (calculated column)
I want to know how much of a reservation is left (In this case 100-25 = 75)
Your help is much appreciated
Solved! Go to Solution.
Hi @Anonymous
I created two columns, but you can do it in one. Sometimes easier to understand in two and debug.
So, logic just get the value and if it is sales, make it negative.
Then sum all based on customer and order id. See table and visual.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
If pos/neg = var Reservation = IF(ORD[Sales Type] = "Reservation",ORD[Sales],ORD[Sales]*-1) return Reservation Left over = CALCULATE(SUM(ORD[If pos/neg]),FILTER(ALLEXCEPT(ORD,ORD[Customer],ORD[Order Id]),max(ORD[If pos/neg])))
Proud to be a Super User!
Hi @Anonymous
I created two columns, but you can do it in one. Sometimes easier to understand in two and debug.
So, logic just get the value and if it is sales, make it negative.
Then sum all based on customer and order id. See table and visual.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
If pos/neg = var Reservation = IF(ORD[Sales Type] = "Reservation",ORD[Sales],ORD[Sales]*-1) return Reservation Left over = CALCULATE(SUM(ORD[If pos/neg]),FILTER(ALLEXCEPT(ORD,ORD[Customer],ORD[Order Id]),max(ORD[If pos/neg])))
Proud to be a Super User!
Thanks so much. This got me on the rigth track.
With some small adjusments i got it to work.
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |