(First of all, sorry for the long context ) I have created the dynamic matrix table (Table1) below for two of the subsidary companies called Apple1 and Apple2. When I select the silcer value, the number changes accordingly. Basically, Apple1 and Apple2 both have similar tool and each tool are used in multiple locations. I created this Matrix table below that summarized the average utilization for each week at each Tool_Location. Column O is the average utilization for the most recent three weeks. My question is, how many Tool_locations are over 90% utilization for the most recent three months per Tool Family. And what is the percentage per Tool Family. Basically, the logic is like (Take A Tool Family as example): Count(Column O>90%)/COUNTIF(Tool_Location, Tool Family="A") = 5/8 =62.5%. The final table looks something like Table 2. The tricky part is that the matrix table is dynamic based on companies, and the last 3 weeks average utilization per Tool_location is also dynamic when week changes. Please let me know if you have any thoughts on this. Much appreciated in advance.
@Anonymous if you can share sample raw data in excel file it will help to get you the solution. You can share data thru onedrive/google drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous hey sorry for the delay, i don't have access to that file, can you reshare
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Helpful resources
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
