Forum Discussion

Nicci's avatar
Nicci
Icon for Helper I rankHelper I
2 years ago
Solved

Get total miles driven in a table

Hi Everyone, I have a table in power bi which is rows for each driving trip users enter. They only enter the ending mileage of the car. What I need to do is show in the table how many total miles dr...
calculation
  • lbendlin's avatar
    lbendlin
    2 years ago

    Here is one example using a measure. Frankly, this could be a calculated column too.

     

     

     

  • lbendlin's avatar
    lbendlin
    2 years ago

    I think a calculated column can solve a lot of your issues.

     

    Try this:

     

    Eff. Mileage = 
var em = [Ending Mileage]
var veh = [VehicleID]
var pem = maxx(filter(all(Mileage),[VehicleID]=veh && [Ending Mileage]<em),[Ending Mileage])
return em-COALESCE(pem,RELATED(Vehicles[StartingMileage]))

     

Nicci's avatar
Nicci
Icon for Helper I rankHelper I
2 years ago

To add, I think its something to do with the dates because in my table i'm listing out the 2 trips individually (which is required because different people drove and we cant combine it so need to know person A drove 27 miles on that day and then later person B drove 32 miles, so cant be combined which I think why yours is working if i combined the display dates together.


lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

I think a calculated column can solve a lot of your issues.

 

Try this:

 

Eff. Mileage = 
var em = [Ending Mileage]
var veh = [VehicleID]
var pem = maxx(filter(all(Mileage),[VehicleID]=veh && [Ending Mileage]<em),[Ending Mileage])
return em-COALESCE(pem,RELATED(Vehicles[StartingMileage]))

 

  • Nicci's avatar
    Nicci
    Icon for Helper I rankHelper I
    2 years ago

    That did the trick, THANK YOU !!!!!!!!!!!!!!!!