- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Matrix Table Hierarchy Display Daily/Monthly/Y...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Matrix Table Hierarchy Display Daily/Monthly/Yearly Result

08-09-2023
01:56 PM

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.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-04-2023
09:11 PM

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! 🙂

23 REPLIES 23

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-15-2023
09:31 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-26-2023
11:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-28-2023
05:26 AM

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)

Proud to be a Super User! | |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-29-2023
09:26 AM

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_NEWhttps://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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-29-2023
12:54 PM

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%

Proud to be a Super User! | |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-29-2023
01:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-31-2023
10:35 AM

My replied on Tuesday was lost? Weird...**Here is result:**https://drive.google.com/file/d/1LXVPHYu7gl1kOGGnUpFRWDB-dxB4flJS/view?usp=sharing

Moving Total Available to the CrossJoinTable provide incorrect result:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-31-2023
12:38 PM

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:

Cheers!

Proud to be a Super User! | |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-01-2023
07:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-04-2023
09:11 PM

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! 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-05-2023
06:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-10-2023
07:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-09-2023
04:00 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-09-2023
04:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-09-2023
05:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-10-2023
04:54 AM

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

Proud to be a Super User! | |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-11-2023
01:57 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-10-2023
06:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-09-2023
03:39 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-09-2023
04:17 PM

*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

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

110 | |

100 | |

98 | |

88 | |

70 |

Top Kudoed Authors

User | Count |
---|---|

166 | |

131 | |

129 | |

102 | |

98 |