Two measures which calculate split share of two different delivery methods

[JesperBT]

Hi everyone,

I'm looking for a "simple" solution that can calculate a split share in a single measure if possible.

Date	Sales Order	Work Order	Delivery Service
18/02-23	123	986578	Truck
18/02-23	123	374884	Parcel
18/02-23	345	734723	Truck
18/02-23	456	235235	Truck
18/02-23	456	363667	Truck
18/02-23	456	834975	Parcel

This is the simplified data. For each "Sales Order" there is an underlying "Work Order" - but if there are muliple deliveries, there will be multiple "Work Order" one for each delivery.

Perfect scenario, there should either be one "Truck" or one "Parcel".

First I need to do a count to get the total amount of "Truck" and "Parcel" orders to get the percentage in the end.

Measure 1 (Total Truck orders): Look through each "Sales Order"/"Delivery Service" and count each unique if it has atleast one "Truck" work order

Measure 2 (Total Parcel orders): Look through each "Sales Order"/"Delivery Service" and count each unique if it has atleast one "Parcel" work order

Measure 3 (Truck Split): Total of each "Sales Order" that has minimum 2 "Truck"  work orders divided by "Measure 1" (to get the %)

Measure 4 (Parcel Split): Total of each "Sales Order" that has minimum 1 "Truck" and 1 "Parcel" work order divided by "Measure 2" (to get the %)

Start Delivery	Split Delivery	2nd Split Delivery	Result (counts towards)
Truck	Parcel		Parcel
Truck	Truck		Truck
Truck	Truck	Parcel	Truck and Parcel (should count towards both)
Parcel			Should only counts towards total, as there is no split
Truck			Should only counts towards total, as there is no split

I'm thinking of creating it like this, one for Truck and one for Parcel. So the first one should count all truck orders and divide it by the orders that has had a truck split and the seconds one should count the parcel orders and divide it by parcel splits.

(In this picture "Split share" is a combination of Truck and Parcel and "Multiple truck share" is just the truck split, but the problem is that the truck split is calculated with using all orders, not just truck orders, so the percent is very small.)

I hope it makes sense, if not, please let me know and I will explain it more in depth.

Thanks in advance,

B

[JesperBT]

Anyone?