Hey everybody,
I don´t know why, but the forum decided to delete my post, so I am just typing it again...
I am stuck with this problem since a while now, have googled and read at least 50 posts, but have not found help so far. I hope, somebody has an idea here.
The question I want to answer is quite simple. How many shipments are needed to fulfil one order.
The dataset I am working with contains information about orders. If you order different product at the same time, for each of these product there will be a record - an order line. But they will get the same order id. Now due to stock levels and other reasons not all order lines of one order are shipped at the same time. Each order lines has an shipping id, which shows in which shipment the order was sent.
So in a simplified version:
| order_id | shipping_id |
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | b |
| 3 | e |
| 3 | e |
| 3 | e |
| 3 | e |
The outcome should look like:
| order id | shipping id | shipping ids per order id |
| 1 | a | 3 |
| 1 | b | 3 |
| 1 | c | 3 |
| 2 | a | 2 |
| 2 | b | 2 |
| 3 | e | 1 |
| 3 | e | 1 |
| 3 | e | 1 |
First, I tried to generate a table for each row which only shows the data with the exact same order id. To check, if my formula is correct, I counted the rows and controlled it manually. That worked:
=COUNTX(FILTER('Outbound_3_ESC',EARLIER('Outbound_3_ESC'[order_id]) = 'Outbound_3_ESC'[order_id]),'Outbound_3_ESC'[order_id])
Then, I tried to count the distinct values for the shipping id in those tables:
Shipments per order_id = CALCULATE(DISTINCTCOUNT(Outbound_3_ESC[shipment_id]),FILTER('Outbound_3_ESC',EARLIER(Outbound_3_ESC[order_id]) = 'Outbound_3_ESC'[order_id]))
The formula works, however the outcomes are wrong. I don´t know what is counted there.... I read in several posts, that this could happen, because I am doing it in a calculated column. And the solution in the forum always was to use a measure. However, in a measure the earlier function is not working... And there I am stucked.
Comments, feedback and solutions are well appreciated. I also am happy to answer any questions, which might appear regarding my problem description.
Thanks in advance for any help!
Jakob
