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]))

     

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

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

 

 

 

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

So I think its close, I am struggling to understand why yours works but when I create a measure and add it to my table its not doing the same thing LOL Here is the crazy numbers its giving me:


The first number 27 is actually correct but thats because its the first and using the starting mileage in the vehicle registration table (yay) but as you can see the numbers just keep multiplying up and up. The second number, 59, is incorrect, it should be 32 and something I found that if you add 32 plus the mileage before (27) it equals 59, but no idea why that is. Is it possible its becuase of the dates being the same so its calculating and adding twice?

Same with the third number, it should be 117, but its giving 172 which is the 117 plus the total before it (59) thats displayed. Aaaaaa LOL