Struggling with my first use of Power Pivot

[Theripped]

I have been trying to learn and implement Power Pivot into my job recently. I've watched a few different YouTube tutorials on it and was able to complete each lesson with the worksheets provided. However, when I try a real world application and use Power Pivot to a workplace project I run into issues.

I have two Excel files. Let's call them "Workbook 1" and "Workbook 2." Workbook 1 has the columns "Employee Name" "Project" "Absence Type" "Shift" for absence type, there are 3 options "Late" "Call-Out" and "No Call No Show" and for "Shift" there are three options "Day" "Evening" "Overnight." Workbook 1 is a long table that tracks employees, the projects that they were absent for, and the specific shift they were supposed to work.

Workbook 2 has the columns "Project" "Total Shifts Day" "Total Shifts Evening" "Total Shifts Overnight" and "Total Shifts" that calculates the total for each Project.

I imported both sources from Excel files into Power Pivot successfully, the information looks good in the Data View. I went to Diagram view and formed a connection with "Project" to show that these are related.

I ultimately want to be able to show the % of shifts that were "Late" "Call Out" and "No Call No Show" for each shift for each Project. However when I go to make a Pivot Table and try to pull data together it says "Relationships between Tables may be needed" Auto Detect says nothing, but I'm not sure what to do when I hit "Create"

Am I on the right track here? What do I need to adjust to be able to achieve my goal of playing around with the two different data sets. Let me know if you need any more information

 

[Matt Allington]

Do ignore that message. It most often comes up when you are trying to use the fact table to filter the dimension table.

Read some of my get started articles here https://exceleratorbi.com.au/knowledge-base/

Specifically, read “the optimal shape for ”Power BI data”

 

[Theripped]

Update, I think I got the relationship established between the two sources correct. However, I am still struggling to come up with a measure that can calculate and show me the percentages of Absence Type for each Project & Shift, based on the total amounts indicated in Workbook 2. I've watched some of the popular YouTube excel tutorials, but they're all very sales based and I'm struggling with applying the methods to this use case.

 

[Matt Allington]

How about you …
post an image of the diagram view,
confirm which columns are used in the relationships,
Create a pivot table that has some meaningful data on rows and columns, and sums the relevant numeric column from your data. Share an image

Then with the above, use that to explain the problem

 

[Theripped]

Ok, apologies for any confusion I cause in trying to explain this.

Workbook1Data - I used Power Query to load-in the first data set "Workbook1Data" This shows Employee Name (omitted intentionally), Project, Absence Type (Call Out, Late, Sent Home, NCNS) and Shift (Day, Evening, Overnight). Also, I went back and changed the data set names in Power Query to match the intended tab names after taking these images.

Workbook2Data - I used Power Query to upload the second data set as well. Workbook2Data shows the same Project List as the first column, however this table shows the total amount of shifts available per shift type per Project as well as the total amount of combined shifts per Project.

Power Pivot - I opened up Power Pivot and it brought in both tables. I went to Diagram View and created a relationship between the Project Columns as those are the same.

PivotTable 1 - Here is the first useful PivotTable I created out of Power Pivot. This shows the total amount of Absence Types per Project. I do not know why Excel isn't sorting this by project correctly, but I'm not that concerned as this is only an example.

PivotTable 2 - This is the 2nd useful PivotTable I was able to create which gets even more granular. This table shows the Absence Type per Shift per Project.

Main Issue - I have not been able to bring the Workbook2 data over into the PivotTable in a useful manner. I ultimately want to be able to see:
- Based on the total available shifts per project, what is the % of absences per project
- Based on the total available shifts per Shift Type (Day, Evening, Overnight), what is the % of Absence Types per Project.
- Will want to see the %s for the grand totals for each table as well.

Those are the main issues I am trying to solve. Eventually, I would like to pull in a 3rd data source that shows every confirmed shift for each Employee and be able to see based off that data the % of absences they have over a given data set. But that's for another day.

Thanks, and let me know if you have any follow up questions!