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

     

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!