Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
Hi @Anonymous ,
Please try to create a calculated column as below to replace that measure and check if that is what you want:
Column = CALCULATE (
DISTINCTCOUNT ( 'Outbound_3_ESC'[shipment_id] ),
FILTER (
ALLSELECTED( 'Outbound_3_ESC' ),
'Outbound_3_ESC'[order_id] = EARLIER( 'Outbound_3_ESC'[order_id] )
)
)
Best Regards
Rena
@Anonymous , Try as new column
calculate(distinctcount(Table[shipment id]), filter(Table, [order id] = earlier([order id])))
Hey amitchandak,
thanks for your reply. This generates still the same result as the previous 2 approaches. I still see 56 instead of 4.
Best Regards
Jakob
Hi @Anonymous ,
Please try to create measures as below to replace the original calculated column:
Measure =
CALCULATE (
DISTINCTCOUNT ( 'Outbound_3_ESC'[shipment_id] ),
FILTER (
ALLSELECTED ( 'Outbound_3_ESC' ),
'Outbound_3_ESC'[order_id] = MAX ( 'Outbound_3_ESC'[order_id] )
)
)
Measure 2 = SUMX(VALUES('Outbound_3_ESC'[order_id]),[Measure])
Best Regards
Rena
Hey Rena,
thanks for your help. That worked!
I want to show the average of the ratio. How could I bring that into a visual?
Do I understand your formula right - Can I simply change the SUMX into AVERAGEX to get the averages into the visual?
AVERAGEX(VALUES('Outbound_3_ESC'[order_id]),[Measure])
Hi @Anonymous ,
Whether the following table is what you want? If no, please provide more details and examples about your expected result.
Best Regards
Rena
Hey Rena,
The idea is to show the average number of shipments per order id in a column chart. Something like this:
If I enter the measure as value for the column chart it shows the sum of all measures separated into years. So I get the total number and not the average in the visualization.
Thanks and best regards
Jakob
Hi @Anonymous ,
Sorry that I still not clear about your requirement. Could you please provide the correct average number of shipments per order id in the form of table just like the one in your original post? As checked your column chart, the date field be displayed as Axis fields. So where this date field is from? Whether it is from the same table with order id field?
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
Best Regards
Rena
Hi @v-yiruan-msft ,
The column chart was just used to give an idea of how the visualization should look like in the end.
The calculation for the measure works perfectly. However, I have problems to bring it into a visualization.
For a calculated column and I can simply choose show as average. However, this is not possible for a measure.
Basically, in the visualization, the shipping ids per order id should be shown as an overall average in a column chart. For the numbers here the average is 2. This should then be represented by one column with the value 2.
The original data set contains more information about year and categories. I am planning to use the year as axis to compare how it developed over time.
Thanks again for your help.
Best Regards
staryuz
Hi @Anonymous ,
Please try to create a calculated column as below to replace that measure and check if that is what you want:
Column = CALCULATE (
DISTINCTCOUNT ( 'Outbound_3_ESC'[shipment_id] ),
FILTER (
ALLSELECTED( 'Outbound_3_ESC' ),
'Outbound_3_ESC'[order_id] = EARLIER( 'Outbound_3_ESC'[order_id] )
)
)
Best Regards
Rena
Hey @v-yiruan-msft ,
this is exactly what I wanted. Great! Thank you so much.
I don´t understand exactly, why the other calculations came to false results. However, with this formula everything is correct and I can use it as planned in the charts.
Thanks for your help and patience!
Best Regards
staryuz
Hey camargos88,
thanks for the fast reply.
The outcome is the same than with my approach - the calculated column shows wrong numbers.
For example there is an order with 130 order lines, but only 4 shipment ids are used in these order lines. However, yours and my formula show 59.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |