Good Afternoon All.
I have recently started using powerpivot for my organisations Time Sheets.
we had previously done this in excel with the formula: =sum(chargeable + Non Chargeable) / chargeable + Non Chargeable + Administration cost))
We are hoping for a similar percentage figure with the powerpivot which can be run from out new live system.
The main issue we are having are the powerpivot brings the figures into a single administration column and the chargeable/non chargeable column is in the form of a "True/Fase".
Would it be easier to split the Chargeable and Non Chargeable coloumn into a numerical field through an IF formula or is there a formula we are missing to create the needed time utilization percentage
hope this makes sense
Thank you in advance
AM
Hi Alasdair,
I'd use Power Query to get your data and then split the chargeable and non chargeable into their own columns using an if statement. Then you can load it into Power Pivot.
This is more efficient for Excel than doing the IF statement in a calculated column in Power Pivot.
Mynda
Hi Mynda
Thank you for the response
I will have a look into using Power Query.
Thanks again
AM
Hi Mynda
We are currently in MS Office 2010 and upgrading to 2016 in a couple of weeks. I understand Power Query is not available in the Standard Office 2010 licence.
We are using a system called NavOne which is a MS Dynamics NAV system. I am also using the Power Pivot to analyse time on various jobs and employees.
One other problem is I have created a date table and added it to Power Pivot. I cannot however get a relationship between the Timesheet data from NavOne and the Data Table. The error is that the relationship cannot be created because each column contains duplicate values.
Hi Alasdair,
You can download and install the Power Query add-in for Excel 2010 here: https://www.microsoft.com/en-us/download/details.aspx?id=39379
It's availble for all versions of Excel 2010. Take no notice of the system requirements.
As for the date table relationship.The clue is in the error message; your date table must contain a list of unique dates. There must be duplicate dates in the date column of the date table.
If you're still stuck, please start a new question under the Power Pivot forum for the date table issue and upload some images of the tables so we can understand what columns are to be related on each table. It might be that you're trying to create a relationship between the wrong columns.
Mynda
