Utilization

[ferbmeister]

Greetings all, looking for some assistance here. I'm new to Power BI and been given a complex task. Here's the task: find the utilization of certain vehicles, from a certain company, in a certain location, and provide the report in a Daily, monthly, Qtrly scenario (the user will select what date range).

Facts:

1. Vehicle company (A, B, C, etc) can have multiple vehicles "checked out".  That count goes against their "on Hand" amount, which differs per location.
2. There are different Vehicle types per company as well as amounts.
3. Vehicles and be checked out different dates per location (i.e. Car from Comp A is out 1/1 to 1/8, while another car could be checked out 1/6-1/10).
4. The check out day varies and there are not set limit.
5. The as long as the "status" is "OPEN", vehicles checked out will count against "on hands".  If status is closed, the vehicle is back in stock/available.
6. The return date will be filled based on the customer's request.  However, they can go over or under that time, this is where the "status" comes in to resolve that.

Vehicle type	#of Vehicle used	Company name	Check out	Return	Days used	Location	Total On Hand	Status
Car	2	A	1/1/2024	1/8/2024	7	Alpha	10	Open
Truck	3	A	2/15/2024	2/23/2024	8	Alpha	5	Open
Bus	4	A	3/5/2024	3/18/2024	13	Alpha	7	Close
Car	7	A	1/5/2024	1/16/2024	11	Bravo	10	Close
Truck	3	A	1/27/2024	2/6/2024	10	Bravo	5	Open
Bus	4	A	2/5/2024	2/7/2024	2	Charlie	7	Open
Car	4	A	1/31/2027	2/7/2024	8	Charlie	8	Open
Car	4	B	2/16/2024	2/25/2024	9	Alpha	6	Close
Truck	1	B	2/18/2024	2/19/2024	1	Alpha	3	Open
Car	1	C	3/2/2024	3/14/2024	12	Delta	8	Open
Car	5	C	3/5/2024	3/14/2024	9	Bravo	8	open
Truck	2	C	2/22/2024	3/1/2024	8	Bravo	4	open
Trailer	1	C	2/22/2024	2/24/2024	2	Bravo	1	close
Truck	1	D	3/2/2024	3/4/2024	2	Charlie	4	Open
Trailer	2	D	1/19/2024	1/31/2024	12	Charlie	4	Open
BUS	2	D	1/1/2024	1/16/2024	15	Charlie	3	Open

I have tried creating a measure that calculates the amount of vehicles given a certain selection from a slice, i.e. date range, company name, vehicle type , etc.  This works, but only shows base on selection.  I want to be able to show a daily monthly utilization, using a Matrix.  With the Matrix, i have done the basic which shows the count for each Company, based on a date, per vehicle type.  You can drill down and see the location.  

I would like to present the utilization both in percentage and fractional ( i.e 4/10) and this is where i'm stuck.  Any help pointing me to the right direction or is this not feasible?

Thanks!

[Anonymous]

Hi @ferbmeister , 

Did the solution TomMartens  offered help you solve the problem, if it helps, you can consider to mark it as a solution so that more user can refet to.

Best Regards!

Yolo Zhu

[TomMartens · Accepted solution]

Hey @ferbmeister ,

unfortunately there is no function that turns the percentage value into a fraction, this is also not possible. This means you have to create a measure that is creating a "string" like so:

var theCount = ...

var theAvailableCars = ...

return

theCount & "/" & theavailable cars

Hopefully, this provides an idea of how to tackle your challenge.

Regards,

Tom