Different Row Hierarchy Calculations in Matrix - I...

vip219 · ‎02-13-2024

Hi all,

I had a question that I have been stuck on for the past few days. I am building a resource allocation model in PowerBI based on a source table that has columns such as Project Name, Category (Type of Project), People Names, Role, Org, and Status (Fulfilled or Unfulfilled), and columns for each week of this year. The Names are not really a unique identifier as there can be multiple rows for the same name if they are working on different projects (can only be a part of one Org though). The values in this original source table are the hours a person is allotted to each project they're on each week. If a person works at org XY, the max number of hours they can work is 36 hours per week; if they do not work at XY, they can work up to 40 hours per week.

I was looking to create 2 matrices on 2 separate pages. The first matrix would have Project Names as the top Row in the Row Hierarchy which can be expanded into Names. The columns of this matrix would be Week (which were the week columns that I unpivoted to get into a single column) and the values for the Project Names rows would be the sum of the hours remaining for all the Names on that project, while the values for the names rows should be each Name on that project's remaining hours (subtracting their total hours allotted from 36 or 40 depending on the org). For the second matrix, I want the same fields but the Rows reversed, where Names is the top level of the row hierarchy expanded into Project Names. On this matrix, I want the top-level Row values for names to show the person's remaining hours for that week, which is the difference between 36 or 40 (depending on org) and the sum of the hours allotted that week among their various projects. I would want the values for the Project Names level to be the number of hours they have allotted to each project they're on that week.

I have tried making several measures or calculated columns to solve this but come across issues between one of the row-level values not being correct.

For example, if Jerry at XY has 8 hours allotted to project Jewel and 4 hours allotted to project Cheetah this week and Sarah at ADY has 2 hours allotted to Jewel and 9 hours allotted to Cheetah, I would want the first matrix to have the Project Names values be 53 for both of these projects (assuming these are the only 2 people on these 2 projects for this example), where the Names values when expanded would show Jerry with 24 (which is 36-12) and Sarah with 29 (which is 40-11). Often, the hours allocated will not be calculated correctly even though I've tried to use a difference measure with Max Hours - Total Hours allocated that week; it will show that Jerry for example has 28 hours left for Jewel and 32 hours left for Cheetah thus skewing the totals, when in reality he only has 24 hours left across deals, so he must have 24 hours left for both projects regardless. For the second matrix, I would want Jerry's name in the top-level Rows to show 24, which when expanded into Deal Names, it would show Jewel with 8 and Cheetah with 4. These are 2 different calculations for the row hierarchy as the second level, Project Names, would just be the hours allotted to each project that week, while the top level, Names, would be 36 or 40 - the sum of those allotted hours.

I have not been able to calculate this properly at all as one or both levels get messed up. I would also like to be able to use slicers to filter this data as needed, especially for Status (fulfilled or unfulfilled) to show the project or Names that have been allocated hours but do not have people filling the roles yet. Could you please help me with this issue? Would be greatly appreciated! I have included some sample data below.Sample Source Data