Forum Discussion
Data modeling and report structure
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
4 Replies
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.
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
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-power-bi
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
