Forum Discussion

BudMan512's avatar
BudMan512
Icon for Helper V rankHelper V
3 years ago
Solved

Delivery Truck Efficiency

I created the Delivery Efficiency report to help determine the performance of drivers and trucks.

Here is a link to my report.

https://ruralcomputers-my.sharepoint.com/:u:/g/personal/bingraham_rccbi_com/EbMS-jiBZw9ElifqH8JZhBQBW9i0anB41w9pwKIIPX-Ggg?e=gaPhs1

 

There is a Trip fact table and a History fact table and they each have different levels of granularity.

The Trip table provides a single entry for each time a driver goes out.  It contains the Driver#, Trip_Units, Trip_Odometer, Hours of the trip and date.

The History table provides a seperate record for each individual delivery made. It contains the Category (or product), Qty, Truck#, date and more.

 

 

I had no problems Calculating Total Stops, OOG (out of gas), No Gas (took no delivery), and regular stops.

I have spent hours trying to calculate Gal/Stop, Miles/stop and Stops/hour.  I was able to calculate the Gal/Hr (Total Units/Hr) as both values are found in the trip table, so no problems.  The  other calculations have values in both fact tables and are not working.  I assume I have a problem with my model but haven't sorted it out.  I would appreciate someone helping me with the DAX and the model if needed.  I appreciate it greatly.  Bud

 

 

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    That looks like a calculated column again.  use Average instead of Sum, or create a measure that uses AVERAGEX.

15 Replies

  • grantsamborn's avatar
    grantsamborn
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hey BudMan512 

    I've looked at your pbix and your requirement and I'm not sure what your end result would look like.

    Can you show examples of what you would expect from the source data?

     

  • BudMan512's avatar
    BudMan512
    Icon for Helper V rankHelper V
    3 years ago

    Hi Grant,

    My end result would look exactly the same as what I sent except the columns highlighted in yellow would have the correct values, which they don't have now.  So below is how those values are determined.  I would like to know,  what would the DAX be for this and is the model ok?

     

    Gal/Stop = Qty/Total Stops,  796.7/13 = 61.3

    Mile/Stop= Miles/Total Stops,  87/13 = 6.7

    Stops/Hour = Total Stops/Hours = 13/8.08 = 1.6

    Thanks,

    Bud

     

     

     

    • grantsamborn's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Hi BudMan512 

      One thing that jumps out is you are creating calculated columns based on measures. 

      I Believe calculated columns use row context when being created.  As an example when creating [Gal/Stop], the measure [Total Stops] is only returning 1 because it is being evaluated with a row context.  (I might be worng with this.)

       

       

      /********************************************/
- Gal/Stop (HISTORY)

	Gal/Stop = 			// calculated column
		DIVIDE(
			[QTY],		// column
			[Total Stops]	// measure
		)

/********************************************/
- Miles / Stop (TRIP)

Miles/Stop = 			// calculated column
    DIVIDE(
        Trip[TRIP_ODOM],	// column
        [Total Stops]		// measure
    )

/********************************************/
- Stops / Hour (TRIP)

Stops/Hour = 			// calculated column
    DIVIDE(
        [Total Stops],		// measure
        Trip[Hours]		// column
    )

/********************************************/

       

       

      Also, I believe that calculated columns are static by nature whereas measures are dynamic.  I'd be careful mixinng and matching like you are.

       

      I recommend checking with some of the SuperUsers in the community.

    • BudMan512's avatar
      BudMan512
      Icon for Helper V rankHelper V
      3 years ago

      Hi Grant, 

      I have tried several iterations of the formulas, I guess these were just the ones I tried last.

      I need help with the solution.  Do you know how I can connect with the super users?

      Thanks for trying.

      Bud