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 have following table:
Ordernumber | Date | Status |
8888 | 25.04.2021 | A |
7777 | 25.04.2021 | A |
1111 | 25.04.2021 | A |
1111 | 25.04.2021 | D |
2222 | 25.04.2021 | A |
2222 | 25.04.2021 | D |
3333 | 25.04.2021 | A |
3333 | 25.04.2021 | D |
4444 | 25.04.2021 | D |
5555 | 25.04.2021 | D |
Order numbers with D are the orders which we accomplished to ship out today already. Status A orders are the delayed leftovers from yesterday which "had" to become D later today. Thats the reason these orders have an "A" and an "D" counterpart. Thats the reason why orders with "A" only gets updated once a day, otherwise they would be overwritten with "D" and you can't tell the real perfomance of today. "A" orders are not counting to the performance of today, because they should have finished yesterday.
So to calculate the performance of today istn't just count of D orders minus count of A orders. Its actually count all "D" orders minus the "D"orders which also has a "A" Status counterpart.
In above sample data 2 should be the outcome.
I hope it was not too confusing.
Thank you in advance!
Best.
Solved! Go to Solution.
Hello @Applicable88
A measure like this should work:
Performance =
VAR Orders_D =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "D"
)
VAR Orders_A =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "A"
)
RETURN
COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )
The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.
With your sample data,
Regards,
Owen
Hello @Applicable88
A measure like this should work:
Performance =
VAR Orders_D =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "D"
)
VAR Orders_A =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "A"
)
RETURN
COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )
The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.
With your sample data,
Regards,
Owen
Thank you again @OwenAuger .
I found the problem. My last function was not right. A simple sum was enough and afterwards it worked out.
Best.
Thank you very much it works for the ordernumbers.
I forgot to mention that I have another column (Positions of deliveries) which this time needs to be sum up base on the above logic, so only sum these delivery positions of the 2 orders. I tried with calculate sum and sumx as well, but I always get an error or empty value and sometimes a much bigger number. As you can see I changed the last line to sum instead of count:
Performance =
VAR Orders_D =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "D"
)
VAR Orders_A =
CALCULATETABLE (
VALUES ( Orders[Ordernumber] ),
Orders[Status] = "A"
)
RETURN
calculate( SUMX(Orders,Orders[Deliverypositions]), EXCEPT ( Orders_D, Orders_A ) )
Do I need a comlete another formula to get sum the position?
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 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |