Matrix Table Hierarchy Display Daily/Monthly/Yearly Result

jaysoulz Glad you figured it out, even if it was not my solution that worked in the end. But if I was that much help, kudos are greatly appreciated :). 

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)

 

I have saved my changes to your most recent model here:

https://www.dropbox.com/scl/fi/p70706u62aa57riippnf0/Utilization-1.pbix?rlkey=otbgn03p5ruzuiqg1t8r6kk7h&dl=0

 

Cheers!

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