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

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_NEW
https://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!

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)