Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

On Time and In Full Overlap Calculation

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. 

 

Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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 

3 Replies

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      That's the one! Thank you!

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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.⚡