Forum Discussion

jaysoulz's avatar
jaysoulz
Icon for Helper I rankHelper I
3 years ago
Solved

Matrix 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 l...
Need Help
  • jaysoulz's avatar
    jaysoulz
    3 years ago

    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's avatar
jaysoulz
Icon for Helper I rankHelper I
3 years ago

 

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

 

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

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
3 years ago

>>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's avatar
    jaysoulz
    Icon for Helper I rankHelper I
    3 years ago

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

  • jaysoulz's avatar
    jaysoulz
    Icon for Helper I rankHelper I
    3 years ago

    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)