Formula for Total Miles in a time period

[sthaya]

Hello all,

I just started using Power BI today. I am using two different Excels files and using a relationship between them to make a Report. One data sheet is from our gas program. Each time a driver fills up at our onsite fuel pump, the program grabs his information, such as gallons and mileage. In this report, I am filtering by drivers and I would like to find out how many miles they put on the vehicles by subtracting 'earliest' mileage info from the current mileage info. So in this screenshot example, I want the formula to be 189,418 - 187,363 to show me the total miles he has driven in this time frame. It's important that this works when I filter the different drivers. Some drivers fill-up gas more often than others. Any help would be greatly apprieciated. Thanks!

[OwenAuger]

Hello @sthaya

Welcome to the forum 🙂

I'm assuming that Card # identifies a driver.

A simple measure that should do what you want is:

Miles Driven =
SUMX ( 
    VALUES ( YourTable[Card #] ),
    CALCULATE ( MAX ( YourTable[Mileage] ) - MIN ( YourTable[Mileage] ) )
)

This measure would sum the Miles Driven for each driver by subtracting the minimum Mileage from the maximum Mileage, so would work whether one or many drivers are filtered.

Also, this measure would work as long as your current filter context includes a range of rows. For example, if you added this measure to the table you posted, it would give you zero on each individual row, but the total would be 189,418-187,363 = 2,055.

Please post back if you need further help.

Regards,

Owen

[sthaya]

Hi Owen,

thanks for your help. That's exactly what I was looking for. However, it doesn't work when I view 'all names'. I think the formula seems to break on blank 'Miles Driven'. This is only happening when my filter is set to ALL. If I select individual names in my filter, this works perfectly. Do you know what is causing this issue? Thanks!

[Ashish_Mathur]

Hi,

Share your miles driven measure here.

[OwenAuger]

Hi again @sthaya

You're welcome!

1. Just checking - is the Miles Driven column a numerical type? If not, you should change it to whole number or decimal number in the Query Editor ideally. (The error message suggests that the column contains text values.)
2. Also, we may need to ensure nulls are handled properly - would you just want to ignore nulls?
   By default MIN & MAX will exclude null values, which I would think is fine for your situation.

 Regards,

Owen

[sthaya]

Hi Owen,

my only issue is that the "Miles Driven" is not part of the original source data - I created that column from the script you gave me in a previous post. So i am unable to edit "Miles Driven" in the Query Editor. Is there another way I can change that to "whole number"?

Thanks, again for your ghelp with this.

[OwenAuger]

Hi @sthaya

Thanks for the reply.

My mistake - what I meant to ask was whether your Mileage column is a numerical type (whole number or decimal)?

If it isn't, could you try making that change and let me know if the measure works?

Regards,

Owen