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

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's avatar
ToddChitt
Icon for Super User rankSuper User
3 years ago

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