Hi,
I was hoping someone could help. I have two sets of data in two seperate tables. One is absence data, and the other is available hours - this is one figure per month. What i`m essentially trying to do is combine these two tables through power pivot so that i can group the absences by month and then have the available hours in another column with a measure to show the absence hours divided by available hours to give me % of absence hours.
I hope that makes sense. I have attached a workbook with both tables and an extra tab showing expected result.
Thanks in advance for any support.
Brendan
Apologies - workbook now attached
Hi Brendan,
Welcome to our forum! Thanks for sharing your file.
First, you need to create a date table that has a consecutive set of dates from 1st Jan 2018 through to 31st December 2018, and possibly further if you plan to use this file for years after 2018. Mark this as the 'Date Table' on the Design tab.
Then you need to create a relationship between the Date table and the Available Hours and Absence data tables.
In your PivotTable, use the Date field from the Date table. Then you can bring the 'available hours' field into the PivotTable and create the measure that calculates the Employee Turnover.
I hope that points you in the right direction, but let us know if you get stuck.
Mynda
Hi Mynda,
Many thanks for getting back to me. This has definately pointed me in the right direction, however, I have hit a wall with the measure. Please see the attached spreasheet.
I`ve managed to get the available hours and absence data into the same pivot table with a relationship set up to the date table.
I think the issue now lies with measure. The measure I have used is =[sum of hours]/[sum of available]. The percentage figure is way off (not really sure what to has come up withto be honest).
I would ideally like to to show a calcualtion of the hours absent for that month only / the hours available for that month only.
I have attached the new model plus pivot (tab titled "my workings"), as well as, an additional tab showing ideally expected results.
Many thanks,
Brendan
Hi Brendan,
Please see file attached. You need to write explicit measures to calculate the total hours and available hours, which you can then use to calculate the percentage.
Note: please don't put % signs in file names as this character prevents the file being opened. Thankfully I was able to download it from our backend, but the file you attached above cannot be opened from this forum post.
Cheers,
Mynda
Hi Mynda,
Many thanks for the reply and for the help - its much appreciated.
Unfortunately I can`t see an attachement - sorry If i`ve missed this.
Thanks,
Brendan
Sorry, uploaded now!
Many thanks - this is really helpful. The measure seem like really simply solutions and I will be able to use this for a few things (like employee turnover) that I`m working through.
Thanks again.
Brendan
