Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
106 | |
87 | |
75 | |
69 |
User | Count |
---|---|
123 | |
112 | |
95 | |
83 | |
73 |