Forum Discussion
Helper IMatrix Table Hierarchy Display Daily/Monthly/Yearly Result
I am facing an incorrect aggregate calculation to display the different levels of the hierarchy from the Matrix table.
I want to show the result daily, monthly and if possible yearly, but looks like PowerBi is summing up the monthly in which created an incorrect result.
The time allowed to use the tool is 10 hours / day for 5 days a week.
My measure: [Utilization] = TimeUsed/10H * 100
Here is the data populated for Feb 2023 by PowerBI Matrix Table:
Daily:
Feb 14, 2023 TimeUsed=0.24,Utilization=2.43%
Feb 15, 2023 TimeUsed= 1.98,Utilization=19.78%
Feb 16, 2023 TimeUsed=2.83,Utilization=28.28%
Feb 17, 2023 TimeUsed=0.09,Utilization=0.93%
Feb 28, 2023 TimeUsed=0.30,Utilization=2.99%
Monthly (February 2023 has 20 business days):
February 2023 TimeUsed=5.44,Utilization=54.40%
As we can see, the daily Utilization is correct, but monthly should be 5.44 / TotalBusinessDay which is 5.44/(20BizDay * 10H) = 2.72%
I tried different calculation, but cannot figure it out how.
Any solution for me?
Greatly appreciate.
Finally found the solution.
No joint was necessary.
Creating a measure to count the number of robots, modify the TotalUsed hours by amending theUtilizationRobot = DIVIDE([Utilization],[RobotCount])
By linking the relationship correctly and all work.
Again, thanks to ToddChitt for the great help! I have learnt a lot! 🙂
23 Replies
- ToddChitt
Super User
Your denominator is off. There are 10 hours per DAY, but 50 per WEEK.
Try this: In your date table, put in a column for Work Hours. Populate it with 10 for working days (M-F) and 0 for weekends.
Now your measure can be:
SUM( Time used ) / SUM ( 'Date'[Work Hours] )
This will work for a daily slice, weekly, monthly, etc.
jaysoulzThanks for the help, but for weird reason. Not all the results are correct:
- ToddChitt
What is the formula for the New Utilization?
I get that the lines total to 5.44, but what is the (20 x 10H) portion?
If each DAY had 10 hours of availability, then the total DENOMINATOR is 5 days times 10 hours = 50. So the total utilization for the week should be 5.44 / 50 = 10.88 %.
What am I missing?
- jaysoulz
DENOMINATOR is 5 days times 10 hours = 50
Yeah, that's for ONE week. Every business day should be counted in one month. So, February 2023 there is 20 business days.
10 hours x 20 business day = 200 hours in total, so data shows 5.44H used for Feburary, so it should be 5.44 / 200 hours = 2.72% Utilization for February 2023
- ToddChitt
Ah, got it. But for the math to work out, you need to have a CONTIGUOUS Date table that has one row for every day regardless of if machines were used on that day.
Please show sample data for both the Duration data (Used and Idle) and your Date dimension.
- jaysoulz
Old Calculation:
Utilization = [TimeUsed]/10*TimeUsed = SUM('DateTable'[DurationWorking])*24 + SUM('DateTable'[DurationIdle])*24*DurationWorking& DurationIdle has date and time, so I had to multiply by 24 to have the hours
New Calculation:Work Hours = IF(WEEKDAY('DateTable'[DateTime], 2) <= 5, 10, 0) as New ColumnNewUtilization = (SUM('DateTable'[DurationWorking])*24 + SUM('DateTable'[DurationIdle])*24) / SUM('DateTable'[Work Hours]) as New Measure- ToddChitt
Can you supply a snippet of a few days worth of data, preferrably from the days in the visual?
Seems you should have a calculate COLUMN of
TimeUsed = (Duration Used + Duration Idle) * 24.
Measure:
TOTAL TimeUsed = SUM ( [Time Used[ )
TOTAL Work Hours = SUM ( [Work Hours] )
Also, I like to use the DIVIDE function instead of a "/" operator like this:
My Divide Measure= DIVIDE ( [Numerator], [Denominator], <optional result>)
So the final measure is:
My New Utilization = DIVIDE ( [TOTAL TimeUsed], [TOTAL Work Hours], 0 )
but yeah, sample data would really help.
- jaysoulz
***My bad working and idle was decimal, so I had to x24 to make it hours.
https://drive.google.com/file/d/10bu0d5DIbbmuzQtxqG2QfLNJ1hCfoKQC/view?usp=drive_link
https://drive.google.com/file/d/1N-pkOnuPEQQxrb-FTQzzmoNKw4xONXmx/view?usp=drive_link
Here is the data for February and March 2023:
https://docs.google.com/spreadsheets/d/1QAYUlHds-yPGfwfilMFk84AOKDpim_04/edit?usp=sharing&ouid=114778904675770620299&rtpof=true&sd=trueAll calculation (column and measure) are created directly from the DateTable. I didnt create any calendar table...
- ToddChitt
I think this will get you there from my Public DropBox:
Let me know.
Please mark answers as Solutions if they get you what you need.
- jaysoulz
ToddChitt
Here is the file. I shrink it down to only 19 "robots"https://drive.google.com/drive/folders/12kt19PsndPBmgfeYOAsHg3I0LMjMc960?usp=sharing
1. If a Robot used the device on weekend which may happen, then it should also be counted toward its utilization. However, that extra hour/day should not be counted toward the business days in each month since it is overutilization. For instance, if an employe work 40H / week, doing extra hour will count as overtime. So, in the example below, Feb 2023 is maxed at 20 days (monday to friday) for the month even if the robots are exceeding its utilization usage.2. Now, is the next stage... Is to get the correct display by Region, etc...
If I select a Region, let say Oceanie, we can see 4 robots are using device...
Let take Feb 03, 2023Digi... 7.21/10H
TB... 7.48/10H
TD... 10.36/10H
TD8...11.72/10H
Total: 36.77/40H = 91.93% (I am seeing 367.68%)
Same if I choose City: Kyoto, there are 5 Robots, let choose Feb 6, 2023:
C-92...nullTD19... 10.01
TD369...10.97
TD456...null
TD8997...null
Total: 20.98 / 50H = 41.94% (not 209.71%) because not all the robots are using the device.
Again, thanks for the help!
- ToddChitt
Hello jaysoulz
A couple of point/questions:
1) In your table for My Dates, your calculation of [Total Available] should be a MEASURE, not a Column. As a column it is simply adding up all the [Available] hours in the whole table and coming up with 2600. (260 Working Days * 10 hours per day). If you use a Measure, you will get the proper 10 hours per day when you put it on your Matrix.
2) Question: Is each 'robot' available to work 10 hours for each day? If so, then whe DENOMINATOR in the Utilization calculation needs to be (10 hours times the number of robots available to work). For this you have two choices:
A) Simply change the calculation of [Available] to [Work Day] * 190. (You have 19 robots in the List so 19 Robots * 10 hours per day per robot = 190). But I bet that is an obfuscated list and you have more than 19. So try the next, and in my opinion, preferred, approach:
B) Do a CROSS JOION of the list of Robots and Dates CROSSJOIN function (DAX) - DAX | Microsoft Learn This will get you every combination of Robot and Calendar Day. To make the join to the Data Table, you now need a two-part key, which is simply a combination of the [IDTag] and [Date] as follows:
Join Key = 'DataTable'[DateTime] & " - " & [IDTag]Do that in both the Data Table and the CROSS JOIN table.With this approach, You can now calculate Utilization PER ROBOT.
Next, you will also need to set the Cross FIlter Direction on the Join to BOTH. That will let the Data Table 'filter' the list of Dates and Robots.
We're getting closer. But as you pointed out, Kyoto has 5 Robots in it, but only 2 of them worked on 2/6/2023. Therefore, the Utilization DENOMINATOR is calculated at 20 (two robots times 10 hours each) instead of 50.
The problem now is that you have missing data in the Data Table to tell you that a Robot was AVAILABLE in Kyoto on 2/6/2023 but did NO WORK. Not sure how to get that in there.
Hope this helps.
(Please mark answers as Solution if appropriate, and/or UpVote)
