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 everyone,
I have the following problem. I have a only table in which I have the following columns: Order number, product, boxes, order date. Per one day we can have more than 1 order in which some of the articles may appear in the both orders but in some case only in the one. Based on the order number we can identify which one came first, which one second and so on.
The goal is to calculate the sum of boxes for the first order, and the sum of the boxes for all other orders.
For example let we have the following scenario:
In this case, in the result we would have
Product | Or. Box | Ex. Box
x | 20 | 0
y | 12 | 5
z | 3 | 0
w | 12 | 0
I am using the following dax, but unfortunately it doesnt work when i have more than 1 date selected.
Extra boxes = CALCULATE(SUM(Orders[Boxes]), KEEPFILTERS(Orders[Date]), FILTER(Orders, Orders[Order] > MIN(Orders[Order])))
Original boxes = CALCULATE(SUM(Orders[Boxes]), KEEPFILTERS(Orders[Date]), FILTER(Orders, Orders[Order] = MIN(Orders[Order])))
Here is the link of the file .
Thank you for your help.
Kind regards,
Stefani Vileva
Solved! Go to Solution.
I have solved the problem in the following way. First I have made a duplicate table in which I have groupped the data based on the dates for each date what is the min and the max order number. After that, using the shown measures I have calculated the values for original and extra boxes. Note on the creating the table for min and max order number, sometimes maybe there could be one order per day without an extra order, in that case I have filtered the table not to take those rows because in that case the values would be summed in both of the measures.
Extra boxes = CALCULATE(
SUM(Orders[Boxes]),
Orders[Order] IN VALUES('Groupped Orders'[MAX_ORDER])
)
Original boxes = CALCULATE(
SUM(Orders[Boxes]),
Orders[Order] IN VALUES('Groupped Orders'[MIN_ORDER])
)
I have solved the problem in the following way. First I have made a duplicate table in which I have groupped the data based on the dates for each date what is the min and the max order number. After that, using the shown measures I have calculated the values for original and extra boxes. Note on the creating the table for min and max order number, sometimes maybe there could be one order per day without an extra order, in that case I have filtered the table not to take those rows because in that case the values would be summed in both of the measures.
Extra boxes = CALCULATE(
SUM(Orders[Boxes]),
Orders[Order] IN VALUES('Groupped Orders'[MAX_ORDER])
)
Original boxes = CALCULATE(
SUM(Orders[Boxes]),
Orders[Order] IN VALUES('Groupped Orders'[MIN_ORDER])
)
Hi, @stefani_vileva
It's pleasant that your problem has been solved. Thanks for your sharing.
Best Regards,
Community Support Team _ Eason
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |