Forum Discussion
Helper IMatrix Table Hierarchy Display Daily/Monthly/Yearly Result
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! 🙂
Helper IToddChitt
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, 2023
Digi... 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...null
TD19... 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!
Super UserHello 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:
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)
- jaysoulz
1. You are right. Amended.
2. The normal time that we allocated is 10H daily, so the robot can work up to 10H or more. Working more than 10H is overtime which will impact the utilization (11H work on 10H daily = 110% utilization)
Now, I am stuck after the 2nd join key added to CrossJoinTable...
A single value for column 'DateTime' in table 'DataTable' cannot be determined....
Utilization_NEW
https://drive.google.com/drive/folders/12kt19PsndPBmgfeYOAsHg3I0LMjMc960The 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.
The slicer AIRobot show us the list of Robot and top right (I didnt dispaly in the picture), I created a table to display the number of AI Robot. If I click on slicer Month Feb and slicer Day 6, we can see that there are 2 AIRobots that work that day.
Mind to post the pbi file so I can see the whole stucture? Thanks!
- ToddChitt
In your Cross Join Table, you have the Join Key column defined as a Measure. It needs to be a Column:
Join Key = [Date] & " - " & [IDTag]Next, set the relationships correctly:* From DataTable to CrossJoinTable on [Join Key], and that should come out as Many-to-One. Then set the Cross-Filter direction to "Both"* From CrossJoinTable to My Dates on [Date], Many-to-OneI want to make sure of your business logic here. In your DataTable, for Kyoto, there are actually 5 Robots listed, but on Feb 6, 2023, there are only 2 robots that have data. So, which of these statements is correct concerning what you expect to see for Utilization for Kyoto on that specific date:
ONE: Since only TWO robots worked, and each robot is given 10 hours per day, then that is 20 hours. The robots actually worked 20.97 hours, so the Utilization is 20.97/20 = 104.85%.
TWO: Because there are FIVE robots 'associated' with Kyoto, the number of AVAILABLE hours is 50 (5 Robots times 10 hours each) so the Utilization would be 20.97/50 = 41.94%
- ToddChitt
Also, In your table of Dates, only [AvailableC] column is correct. Remove [Total Available C] measure.
Next, the MEASURE of Total Available should be in the Cross Join Table. It should SUM ( [Available C] ) in the Cross Join Table