Data modeling and report structure

[ldrysdale13]

Hi, 

I am trying to create a visual similar to the picture below using the data in my spreadsheet. This report will be used for scheduling purposes to show workload by employee. The desired report format shows by employee (and by project), the employees total estimated time scheduled for each category (there are 6 different categories). For each category, there is an Employee Name column and an Hours column. An employee could fall within one category or multiple categories. I have attached a sample data set. Ultimately I would like to be able to see by employee, the total hours for each category for each project. All my data is in one spreadsheet and am unsure how to structure the data and relationships within power bi to achieve this.

Desired Visual

Desired Report

Data sample

[amitchandak]

@ldrysdale13 , where is the category column in your data?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[ldrysdale13]

There are 6 Category columns show in in the Sample Data image above - Horizontal Lead/PM, Design, Plan Production, Stormwater/Support, Landscape Architecture, LA support

[amitchandak]

@ldrysdale13 , if you want to deal with these 5 using role-playing dimension then refer to this - https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

You can create a common table like

distinct(union(distinct(table[Horizontal Lead/PM]), distinct(table[Design]), distinct(table[Plan Production]), distinct(table[Stormwater/Support]), distinct(table[Landscape Architecture]), distinct(table[LA support])))

Star schema is best. You can take out few dimension from this table -  https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

In this series, in the first few videos( 2 and 3rd), I have broken a single table into the star

https://www.youtube.com/watch?v=kU2M1LmNvNo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=2

[ldrysdale13]

Thank you for your response. I tried to use the role-playing dimension and created separate tables from my original table for each category. I also pulled in an Employee fact table that has a relationship to each category table through Employee Name. This seems to work OK from an overall employee/category standpoint, but I am struggling on how to also incorporate the Project related information. I also created a project fact table from my original table but struggling on how to also get the correct project data (specifically project number) into my visuals.