Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Anyone?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |