Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
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
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!
Hi,
Share your miles driven measure here.
Hi again @sthaya
You're welcome!
Regards,
Owen
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.
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
User | Count |
---|---|
83 | |
77 | |
72 | |
70 | |
55 |
User | Count |
---|---|
107 | |
98 | |
86 | |
79 | |
67 |