Hi!
I have this pbix file left by a developer that the company hired before that reports KPI (Effective Utilization) which computes the % utilization of the trucks used for delivery, but only for trucks that have assigned target trips.
Effective Utilization is made up of these measures:
Effective Utilization = [Utilization]/[Availability]
Utilization = [Trips]/[Target Trips]
Availability = 1-([Hours Not Available]/[Target Hours])
Hours Not Available = [Target Hours]-[Hours Worked Completed]-[Hours Worked Cancelled]+[Hours Extra HC Splitted]+[Hours Late Splitted]-[Hours Exempted 2]
Looking at the measures, bulk of the results is based on the Availability measure, which I will focus on in this post.
So the visuals the developer used for his file are mainly matrix tables, which meant not much insights to get from since it's purely data tables that his showing.
The department who requested for this type of report now wants to see bar graphs or charts per day and per month.
However, when I used a bar graph that shows the Availability % per day, the numbers showing were not matching with the Availability % per day in a matrix table. I tried playing around with the filters, and eventually, the Availability % per day graph chart finally matched with the numbers of Availability % per day in a matrix table, but only if the matrix table was not filtered to "Target Trips > 0". With that, I assumed that the "Target Trips > 0" filter is not being applied on the graph chart, even though it's turned on (in the filter pane).
To illustrate, below is a page I made showing matrix tables and charts for Availability per Day and Availability for the Selected Period. Visuals with yellow titles are those with filter "Target Trips > 0" while those in red are those without the "Target Trips > 0". As you can see, the numbers of the yellow graphs are matching with the red matrix tables, but what I want is for the numbers of the yellow graphs to match with the numbers in the yellow matrix tables.
Another problem I actually have is with the Target Hours.
If the matrix table does not have a "per day" day like the one below, there's no problem with the target hours, because it shows correctly that ther are 72 hours (24 * 3 days) target hours per plate number
However, it's a different story when I convert the visual into a 'per day' data. This was brought up because looking at the Availablity % per day, by default, the number shouldn't be more than 100% available. So looking at the measures, the reason why it's becoming more than 100% is due to the negative 'Hours Not Available'.
Availability = 1-([Hours Not Available]/[Target Hours])
Hours Not Available = [Target Hours]-[Hours Worked Completed]-[Hours Worked Cancelled]+[Hours Extra HC Splitted]+[Hours Late Splitted]-[Hours Exempted 2]
Breaking down the hours not available, it looks like the table is only showing 24 Target Hours for selected rows. I'm guessing this is due to how the previous developer made the measure of Target Hours which is
Target Hours =
24 * COUNTROWS ( CutOff )
* COUNTROWS ( DISTINCT ( fTMS[RealPlate] ) )
and the relationship between the Cut-off Period, Calendar Period, and the fTMS table (Date field from Calendar Period has an active relationship with Date_BackinPlant field from fTMS), which I guess, it only shows 24 hours if that date is a date_backinplant that the plate number has.
My question is how to fill the rows with 24 hours because even on days that are not the backinplant of the plate number (or the truck), the truck still has a target hours of 24 hours.
So for this post, I have 3 questions:
1. How to match the numbers of the charts with the numbers in the matrix tables.
2. How to have the plate number still have a 24 hours target trip on dates even though this is not their date_backinplant.
3. Should the initial measures made by the developer be modified? If yes, should the Utilization and Other Utilization be also modified then? Or should separate measures be done for the 'per day' bar graphs and for the Target Hours?
Hopefully, somebody would understand and can help me!
Below is a link of the sample pbix I made for my questions.
https://drive.google.com/file/d/1Wb4mrVhQiKLEBqWiGHeFcytskUI3DFQ4/view?usp=sharing
Tables in the pbix:
Table Cutoff - is like a Calendar table but has a column that classifies which dates would fall into their "cut off period". For example, they labeled "March" dates from Feb 28 to March 29.
Table RealPlateList - is just a created table in which "RealPlate_List = DISTINCT(fTMS[RealPlate])"
Table fTCFleetNeg - table with monthly data listing plate numbers with Target Trips (Trip_Incentives field)
fTMS - fact table that lists down details per delivery
I was able to make a measure for the 24 hours issue I was having. Here is my measure:
Target Hours 2 FIX =
Var Hours = CALCULATE(SUMX(VALUES('Calendar'[Date]),24))
Return
CALCULATE (
SUMX (
SUMMARIZE ( fTMS, RealPlate_List[RealPlate], "24H", Hours),
[24H]
),
CROSSFILTER ( fTMS[date_backinplant], 'Calendar'[Date], NONE )
)
Hi @AllisonKennedy !
Sorry, I should have also explained why the measures were structured this way so the report can be understood better. Taking the Hours Worked Completed as an example... based on the formula, it uses the date/time backinplant and inline fields to know how many hours the truck has worked. The inline and the backinplant gives us the 'from' and 'to' dates.
Example for AAV4465, on the first row, it shows the truck worked from May 30, 2020 at 3:36 pm to June 2, 2020 at 1:18 pm. If the reader would only want to show the Hours Worked Completed from June 1 to 3 on the report, the report should show 24 hours worked for June 1 and 13.30 hours worked
Here's another example for ABH1091, which shows total of 57.35 hours worked from June 1 to 3.
Below is a visual of Working Hours Completed measure per plate number per day showing the correct numbers, just like how they were computed manually above.
Hopefully, this explanation of the Hours Worked Completed has helped you understand why the measures affecting "Hours Not Available" were built that way. As explained by another user in this community to whom I've also presented this measure of Hours Worked Completed, he mentioned that the measure works fine based on what we want.
Moreover, based on your comment of
"Your charts are different to your matrix because of the fields you are using and the measure totals difference. If you remove the Real Plate from the matrix visual, you'll see Availability matches the column chart. Fixing the underlying measures so that they work for your desired scenario (or all scenarios) will help that. ",
It's acually the column charts that are displaying the wrong numbers. The charts' Availability % should match with the Availability % in the matrix tables because both the column chart and the (yellow) matrix tables have a filter affecting them (found in the filter pane) which is 'Target Trips is greater than 0'.
Just to explain exlain the role of the fTCFleetNeg table....not all plate numbers from fTMS table are in the fTCFleetNeg table, while the fTCFleetNeg table is the table that shows the target trips per month for each plate number. Only those plate numbers with target trips should be included in reporting/computing for the Availability %, Utilization %, and Effective Utilization %. Hence, the reason why the matrix tables and charts are filtered 'Target Trips is greater than 0'.
Going back, the reason why I assume that the 'Target Trips is greater than 0' filter is not affecting the column chart is because if I do a matrix table that has NO filtered 'Target Trips is greater than 0', that unfiltered matrix table matches the numbers with the filtered column chart.
Again, it looks like only the matrix table is being affected by the filter, and not the column chart. But I really need the column chart to be affected by the filter.
That is why I assumed maybe an adjustment is needed for the 'Target Trips' measure in order for the column chart to be affected.
Also, would you have a comment/solution on my 2nd question from the original post - "2. How to have the plate number still have a 24 hours target trip on dates even though this is not their date_backinplant.". The general idea is that each truck each day should have a target of 24 hours.
I was able to make a measure for the 24 hours issue I was having. Here is my measure:
Target Hours 2 FIX =
Var Hours = CALCULATE(SUMX(VALUES('Calendar'[Date]),24))
Return
CALCULATE (
SUMX (
SUMMARIZE ( fTMS, RealPlate_List[RealPlate], "24H", Hours),
[24H]
),
CROSSFILTER ( fTMS[date_backinplant], 'Calendar'[Date], NONE )
)
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
