Measure on-time delivery performance and slice by origin location

[jhenscheid]

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.

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

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

[jhenscheid]

This worked exactly as needed!!  Thank you for your help.  Much appreciated!