Forum Discussion

sthaya's avatar
sthaya
Frequent Visitor
8 years ago

Formula for Total Miles in a time period

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 d...
Need Help
OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
8 years ago

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's avatar
sthaya
Frequent Visitor
8 years ago

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!

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      sthaya
      Frequent Visitor
      8 years ago

      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's avatar
        OwenAuger
        Icon for Super User rankSuper User
        8 years ago

        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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Share your miles driven measure here.