Matrix Table Hierarchy Display Daily/Monthly/Yearly Result

[jaysoulz]

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.

[ToddChitt]

@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?

[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, 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!

[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)

[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/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!

[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-One

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%

[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

[jaysoulz]

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:

[ToddChitt]

You ready for this? Another CROSS JOIN with this text:

City_Dates = CROSSJOIN(

    SUMMARIZE('List', List[City], "Number of Robots", COUNT('List'[IDTag])),

    'My Dates'

    )

Then add these two columns:

Hours Per Day Per City = [Number of Robots] * [Available]

JOIN Key City Date = [City] & "-" & [Date]

And this measure:

Total Available = SUM( 'City_Dates'[Hours Per Day Per City])

Then add that JOIN Key column above to the other CROSS JOIN table, and create a relationship there. Make it BOTH ways filter.

Finally, your calculation for Utilization:

Total Used = (SUM('DataTable'[DurationWorking]) + SUM('DataTable'[DurationIdle]))*24 (you have this already)

Utilization = DIVIDE( [Total Used], [Total Available], 0)

I have saved my changes to your most recent model here:

https://www.dropbox.com/scl/fi/p70706u62aa57riippnf0/Utilization-1.pbix?rlkey=otbgn03p5ruzuiqg1t8r6k...

Cheers!

[jaysoulz]

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). 

[jaysoulz · Accepted 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! 🙂

[ToddChitt]

@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 :). 

[ToddChitt]

I think this will get you there from my Public DropBox:

https://www.dropbox.com/scl/fi/3r806b8b69ph38biozsog/utilization.pbix?rlkey=ubr09vi2sl4x42hk4rvfg0mz...

Let me know.

Please mark answers as Solutions if they get you what you need.

[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 Column

NewUtilization = (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=11477...

All calculation (column and measure) are created directly from the DateTable. I didnt create any calendar table...

[ToddChitt]

>>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:

My Dates = CALENDAR("2/01/2023", "2/28/2023")

Now add a calculated column: 

Work Day = IF(WEEKDAY([Date]) IN {2,3,4,5,6}, 1, 0)

And another: 

Available = [Work Day] * 10

And finally a Measure: 

Total Available = SUM('My Dates'[Available])

Next, make sure there is a relationship between My Dates and your data table. If you have multiple rows for any one day, then it will be a one-to-many relationship. That's OK.

In your data table, add assuming you have calculated Time Used, then create a measure:

Total Used = SUM('Data Table'[TimeUsed])

Now, Utilization is simply one measure divided by the other:

Utilization = DIVIDE( [Total Used], [Total Available], 0)

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.

[jaysoulz]

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:

Utilization = DIVIDE( (SUM('DateTable'[DurationWorking]) + SUM('DateTable'[DurationIdle]))*24, SUM('My Dates'[Available]), 0)

[jaysoulz]

Dang. This looks interesting. Do you mind to attach the files. It will be easier for me.  A big thanks for the help!

[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