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

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

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

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

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

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