On Time and In Full Overlap Calculation

[Anonymous]

Hi All,

I know this should be a relatively easy calculation, but I am struggling on sorting out the DAX for it.

I have a table that tracks shipments. I have one column that indicates if a shipment left our building "On Time" or not. Y or N values.

I have a second column that indicates if the orders was shipped in full or not. Y or N values as well. Example:

I have created measures to provide me the totals. IE On Time = 4500, Late = 1200, In Full =4000 and Short Ship = 1700. Total shipments = 5700

I'm trying to figure out how to calculate the total number of shipments that were both On Time and In Full. I'm struggling on what the DAX should be in order to count the overlaps and then divide by the total to get the percentage. 

[parry2k]

@Anonymous try this measure:

ON Time and Full = 
CALCULATE ( COUNRTOWS ( ShipmentTable ), 
    ShipmentTable[OnTime] = "Y",
    ShipmentTable[ShippedFull] = "Y"
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[richbenmintz · Accepted solution]

Hi @Anonymous ,

Pretty sure something like

% of On Time Filled =
var _on_time_in_full = Calculate(countrows('shipment table'), filter('shipment table', 'shipment table'[on_time] = 'Y' && 'shipment table'[on_time] = 'Y'))
var _all = countrows('shipment table'))
return 
divide (_on_time_in_full, _all)

would work for you.

Hope this Helps 

[Anonymous]

That's the one! Thank you!