Forum Discussion
Measure on-time delivery performance and slice by origin location
I have transporation data and am displaying the carriers' performance for delivery. However, I am trying to slice the data by where the load/trip originates. Because I am measuring delivery performance, whenever I try to slice by the origin location, no data comes back.
For example, trip 123 is on time and is hauled by carrier ABC and delivers to City A. It originated in City B. If I put city B in the slicer, nothing comes back.
I deal a lot with transporation measurements and have not seen many posts pertaining to transportation topics, so if anyone needs an interpretation of the lingo, please ask for clarification.
Here is a small part of the data I am working with;
| LoadID | Pick Location | Type | Carrier | Late Load Indicator |
| 62180435 | AWAR | Pick | GRTG | On-Time |
| 62180435 | MFCDA4 | Drop | GRTG | On-Time |
| 62194074 | AWEC | Pick | CELC | On-Time |
| 62194074 | KFLBR2 | Drop | CELC | On-Time |
| 62260282 | AFUWM | Drop | GRTG | Late |
| 62260282 | AWLE | Pick | GRTG | Late |
| 62266674 | AWAR | Pick | PEHL | Late |
| 62266674 | MSSGR | Drop | PEHL | On-Time |
| 62266674 | RACCI27 | Drop | PEHL | On-Time |
| 62266674 | RN0CI | Drop | PEHL | On-Time |
| 62284257 | AWAR | Pick | TTLQ | Late |
| 62284257 | MTSRO | Drop | TTLQ | On-Time |
| 62361052 | ANTO | Drop | TTLQ | Late |
| 62361052 | AWAR | Pick | TTLQ | Late |
| 62365342 | JCODA4 | Drop | KELW | On-Time |
| 62373595 | AWAR | Pick | TTLQ | Late |
| 62373595 | MTSRO | Drop | TTLQ | On-Time |
| 62395979 | AWEC | Pick | SWFT | Late |
| 62395979 | CADCO | Drop | SWFT | On-Time |
So carrier PEHL, for example, is late for the Pick on LoadID 62266674, but on time for the Drop. I am measuring this as on time on the delivery, but want to slice on the Pick location code.
Any thoughts on this or assitance would be appreciated.
Hi jhenscheid,
In source table, create a calculated column that returns the original location for each Carrier per LoadID.
Original lacation = CALCULATE ( LASTNONBLANK ( 'delivery performance'[Pick Location], 1 ), FILTER ( ALLEXCEPT ( 'delivery performance', 'delivery performance'[LoadID], 'delivery performance'[Carrier] ), 'delivery performance'[Type] = "pick" ) )
Create an extra table which lists all distinct values of Pick Location.
Pick location table = VALUES('delivery performance'[Pick Location])Create a one to many relationship between this new table and source table based on [Original Location].
In report page, rather than adding [Pick Location] from source table into slicer, you should add 'Pick location table'[Pick Location] into slicer. Then, when you select pick location item, the visual won't return none.
Best regards,
Yuliana Gu
3 Replies
v-yulgu-msftMicrosoft Employee
Hi jhenscheid,
In source table, create a calculated column that returns the original location for each Carrier per LoadID.
Original lacation = CALCULATE ( LASTNONBLANK ( 'delivery performance'[Pick Location], 1 ), FILTER ( ALLEXCEPT ( 'delivery performance', 'delivery performance'[LoadID], 'delivery performance'[Carrier] ), 'delivery performance'[Type] = "pick" ) )
Create an extra table which lists all distinct values of Pick Location.
Pick location table = VALUES('delivery performance'[Pick Location])Create a one to many relationship between this new table and source table based on [Original Location].
In report page, rather than adding [Pick Location] from source table into slicer, you should add 'Pick location table'[Pick Location] into slicer. Then, when you select pick location item, the visual won't return none.
Best regards,
Yuliana GuThis worked exactly as needed!! Thank you for your help. Much appreciated!
Hi,
I try to build a measure that will return the percentage of items delivered from the total articles ordered. I have two aggregations level: order ID and article ID.
my DAX in Visual Studio, is:
% Deliv.from Order:=
AVERAGEX
(
values(Orders[Order Id]),
AVERAGEX
(
VALUES ( 'Articles'[Article Id] ),
CALCULATE
(
if
(
sum(Deliveries[Delivered Qty]) >0 && abs( sum(Deliveries[Delivered Qty]) - SUM ( 'Orders Lines'[Ordered Qty] )) / SUM ( 'Orders Lines'[Ordered Qty] )<=0.03, 1, 0
)
)
)
)it's working to the article level, but not to the order level
this formula have to return in grand total: 94.74%
article count 19 article not delivered 100% 1 =(I32-I33)/19 94.74% look below table:
table : order lines (line by line - article)
Row Labels Ordered Qty AY Delivered Qty AY Delivered Qty For SL Service Level Qty AY % Deliv.from Orde 2001043689823 (no of order) FITNESS STRAWBERRY 23.5G 2,064.00 2,064.00 2064 100.00% 100.000% FITNESS SIMPLU BATON 23.5G 3,440.00 3,440.00 3440 100.00% 100.000% NESQUIK CEREALE 250G 672.00 640.00 640 95.24% 0.000% CHOCAPIC CEREALE 250G 1,056.00 1,056.00 1056 100.00% 100.000% CHEERIOS CEREALE 250G 864.00 864.00 864 100.00% 100.000% CINI MINIS CEREALE 250G 384.00 384.00 384 100.00% 100.000% CINI MINIS 500 G 120.00 120.00 120 100.00% 100.000% CORNFLAKES NESTLE 500G 816.00 816.00 816 100.00% 100.000% NESQUIK DUO CEREALE INTEGRALE 460G 900.00 900.00 900 100.00% 100.000% NESQUIK DUO CEREALE 225G 960.00 960.00 960 100.00% 100.000% STRAWBERRY MINIS 500G 180.00 180.00 180 100.00% 100.000% FITNESS FRUCTE CEREALE 425G 480.00 480.00 480 100.00% 100.000% FITNESS CEREALE 425G 900.00 900.00 900 100.00% 100.000% NESQUICK ABC 460G 540.00 540.00 540 100.00% 100.000% FITNESS DELICE WHITE CHOCO 22.5G 1,376.00 1,376.00 1376 100.00% 100.000% FITNESS CHOCO CEREALE 425G 240.00 240.00 240 100.00% 100.000% CHEERIOS OATS 400G 792.00 792.00 792 100.00% 100.000% CORN FLAKES MIERE SI ARAHIDE 450G 576.00 576.00 576 100.00% 100.000% FITNESS PEACH APRICOT BATOANE CEREALE 23.5G 2,064.00 2,064.00 2064 100.00% 100.000% Grand Total 18,424.00 18,424.00 18392 99.83% 0.049% = it is wrong (here have to be 94.74% Could you please help me to correct this DAX formula to obtain correct data on the order level?
Sincerely
Alina
