Morning,
I've had a couple of false starts with setting up my data for a dashboard and have decided to start again and could do with some help in figuring out the best way to set it all up.
I had the hours data, and the cost data in 2 separate worksheets, which I think has caused most of my problems. I'm also going to be adding in Pension (employee and company), ASH, NI (employee and company) and Analysis of the time/cost against production further down the line.
For now, I am looking to show the hours of overtime total by department and then hours of each employee (over 80) has worked. I need to calculate the cost of that overtime and be able to breakdown showing 13 weeks - using only two slicers to select current 13 week period and by employee. I need both slicers to change the information simultaneously.
At present, my hours' data is set out in columns as follows:
Department/Employee Name/Total Hours/Qtr1/Qtr2/Qtr3/Qtr4/July/Aug etc../30.06.18/07.07.18/14.07.18 etc..
My cost data is pretty much set up the same apart from an additional column for the overtime rate, and formulas added.
There is an ever-increasing amount of data, and the hours are manually updated fortnightly, so I also need to be able to get to the next entry column quickly.
Hi Melanie,
You shouldn't have separate columns for the quarters/months etc. This is the cause of your problems. You should have one column with the date of the overtime and a separate column with the number of hours. If you only have the hours aggregated by month, then the date column can have one date for each month.
Here is a tutorial on tabular data, which is what you need for PivotTables. You can unpivot your data using Power Query.
This tutorial covers connecting Slicers to multiple PivotTables, and here is a tutorial on the Pivot Cache, which is what restricts how Slicers connect to PivotTables.
If it doesn't make sense for your data to be stored in one table, then you'll need to use Power Pivot instead of regular PivotTables. You can find out what Power Pivot is on my Power Pivot course page/
I hope that points you in the right direction.
Mynda
Thanks Mynda, I'll have a crack at it again - really do appreciate the help.
