I have labor billing file that I upload monthly. Some times I get the duplicate rows with number of hours worked and amount billed per employee when they approved at different times. Currently unique row filter identifies the duplicate by employee id and keep the largest billed amount. This is causing the issue as total expense of that employee reported in Labor report is less.

If I disable the unique row filter I get the correct amount, then I run into head count issue. Any suggestions for resolving this?

Thanks in advance!

Prem

Hi Prem,

There are a few different options depending on how much granularity you currently have within labor reporting and how much you want going forward. A few questions:

1) How granular is your labor reporting today?

2) Do you have employees that work at multiple locations?

Thanks,

Jonathan

Hi Jonathan,

>1) How granular is your labor reporting today?

Labor report can be drilled from cost center to role level grouping of expenses.

>2) Do you have employees that work at multiple locations?

No multiple locations

I get the head count problem in the KPI that shows on the header of multiple pages. This is currently being done by row count, probably need a way to detect duplicate labor ID, something like DISTINCT COUNT on Labor ID.

Thanks

Prem

In the data set, have you tried using the Group By: selection (image below) on the Employee ID column?  Apptio will group all resource entries that have a common Employee ID and sum all numeric Types (hours & amount billed).  If the same resource does have different Label types for other columns, Apptio will report as {Various}.  If you need to differentiate entries on more than just Employee ID, create a new column to group on and concatenate the necessary columns.

After grouping on Employee ID, a new column identified as .Count will be created that you can then reference to normalize the head count from two or more for multiple entries down to one.

]

Thanks,

Brian

Hi Brian,

Thank you. I thought of this but wondered as it will create {Various} in Unique ID.  I went ahead and applied Group By on Labor ID, not sure if {Various} in Unique ID broke something.

Thanks

Prem

Another option to use sumif function to adjust the headcount. One person only can be one headcount so the formula on the headcount column should be something like this: =1/sumif(Labor ID, Labor ID, Count) Where Count being the line count, you can create a new column and equal it to 1 to calculate how many time this labor ID is repeating.

Hope this helps.

Hi Aydan, This works for me. Thanks/Prem

Great! Glad, I could help.