Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
Finally found the solution.
No joint was necessary.
Creating a measure to count the number of robots, modify the TotalUsed hours by amending the
UtilizationRobot = DIVIDE([Utilization],[RobotCount])
By linking the relationship correctly and all work.
Again, thanks to ToddChitt for the great help! I have learnt a lot! 🙂
@jaysoulz If you are still having trouble, can you obfuscate your data and post a copy of the model for us to see what your are doing?
Proud to be a Super User! | |
@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, 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!
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:
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)
Proud to be a Super User! | |
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/12kt19PsndPBmgfeYOAsHg3I0LMjMc960
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.
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!
In your Cross Join Table, you have the Join Key column defined as a Measure. It needs to be a Column:
I 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%
Proud to be a Super User! | |
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
Proud to be a Super User! | |
My replied on Tuesday was lost? Weird...
Here is result:
https://drive.google.com/file/d/1LXVPHYu7gl1kOGGnUpFRWDB-dxB4flJS/view?usp=sharing
The answer to your previous question is 2:
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%
Moving Total Available to the CrossJoinTable provide incorrect result:
You ready for this? Another CROSS JOIN with this text:
I have saved my changes to your most recent model here:
Cheers!
Proud to be a Super User! | |
Thanks ToddChitt for the file! I got lost with so many joins and following your guide I didnt get to the same result, luckily your file make it clearer.
By looking at your result, I think we are spreading in every place... I am not even sure if we need to do the join to be honest.
The main goal is to get the utilization of the robots for day, month. Utilization group per city, region and country. The calculation should be easy enough easy:
Total Used / (Total Robots * 10hours * Working Day).
The Total Robots should be distinct count from table List "A PART"
We have to make sure that it is the Total Robots and not the number of working robot.
So, for Kyoto, I have 5 robots, Montreal 2, New York 3... Which I indicate in the table (top right).
If we follow that equation, we should get the correct result for all. My bad for all the confusion (I got confused myself).
Finally found the solution.
No joint was necessary.
Creating a measure to count the number of robots, modify the TotalUsed hours by amending the
UtilizationRobot = DIVIDE([Utilization],[RobotCount])
By linking the relationship correctly and all work.
Again, thanks to ToddChitt for the great help! I have learnt a lot! 🙂
@jaysoulz Glad you figured it out, even if it was not my solution that worked in the end. But if I was that much help, kudos are greatly appreciated :).
Proud to be a Super User! | |
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.
Proud to be a Super User! | |
Old Calculation:
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.
Proud to be a Super User! | |
***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=11477...
All calculation (column and measure) are created directly from the DateTable. I didnt create any calendar table...
>>All calculation (column and measure) are created directly from the DateTable. I didnt create any calendar table...<<
And therein lies your problem.
How do you calculate the utilization for a day that had no Time Used? Let's look at the 5 rows for February only.
What is the utilization for, say, 2/13/2023? What is the denominator for that? You don't have one.
Create a Date table with this statement:
In the screenshot above, notice how we can calculate Utilization for any individual day that has Time Used, AND the monthly total is accurate.
The key is to have a CONTIGUOUS DATE TABLE (one row for every day in the calendar) like i said in one of the early responses.
Proud to be a Super User! | |
Not sure if I can say working or not. When I select another employee, I am seeing discrepancy, so looks like 95-99% work. Really thanks for the help!
I had to mod the formula:
Dang. This looks interesting. Do you mind to attach the files. It will be easier for me. A big thanks for the help!
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?
Proud to be a Super User! | |
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
66 | |
55 |