Count number of Employees + Productivity - Refined

[Anonymous]

Hi everyone, I have a question that consist of 2 parts. Part 1 is count active Employees over a given period. Part 2 is to check if the company is more productive with more/fewer employees.

PART 1

I would like to count the number of active employees in a given period.

I have a facttable: FactHarvest which is a table where employees fill in their time registration, it looks like this:

Date	Client	Project	Hours	Billable	Billable Rate	Billable Amount	Task	Type	NameEmployee
dinsdag 1 januari 2019	Data Wise	Cloud Architecture	8	No	86	688	Intern	Non-billable	Klaas Wouters
Woensdag 6 februari 2019	AbInbev	Customer interaction PoC	8	Yes	90	720	Billable Customer Time	Billable	Klaas Wouters
Vrijdag 1 maart 2019	Data Wise	Pyspark	8	Yes	86	688	Intern	Non-Billable	Astrid Lemmens
Vrijdag 1 maart 2019	Data Wise	Pyspark	8	Yes	86	688	Intern	Non-Billable	Klaas Wouters
Vrijdag 5 april 2019	Ab Inbev	Customer interaction PoC	8	Yes	90	720	Billable customer time	Billable	Klaas Wouters
Vrijdag 5 April 2019	Data Wise	Pyspark	8	Yes	86	688	Intern	Non-Billable	Astrid Lemmens
Vrijdag 5 April 2019	Airport Company	Pipeline	8	yes	86	688	Billable customer time	Billable	Randy cuppens
Maandag 8 April 2019	Data Wise	Pyspark	8	Yes	86	688	Intern	Non-Billable	Astrid Lemmens
Maandag 8 April 2019	Airport Company	Pipeline	8	yes	86	688	Billable customer time	Billable	Randy cuppens

And I have a dim table with all of the employees, further there is no information in this table! (So no start and end date per employee)

Name

Klaas Wouters

Astrid Lemmens

Randy Cuppens

In the Sample data a new Employee has joined the company on vrijdag 1 maart 2019 (Astrid Lemmens) and on vrijdag 5 April 2019 (Randy Cuppens). Also Klaas Wouters left on Maandag 8 April 2019 as we don't see that name anymore. The end goal would be a (line)graph: X-Axis Month, Y-Axis number of Employees. The line graph should show the trend of employee growth/decline like this: 

PART 2

In the second part we want to check if the increase of Employees has a positive affect on Productivity. So we want to:

- Divide revenue by TotalHours worked or by Total Employees of that period

- Divide Costs by TotalHours worked or by Total Employees of that period

Herefor I have another Facttable used, this fact table's grain: One row is one accountant LineItemover a month.
FactSilverfin looks like this:

Line Item	Month	Value	True Value	Uitgave (Outcome)	Inkomsten (Income)	Monthname	Level
749000 Diverse bedrijfsopbrengsten	PTD 01/2019	-763,22	-763,22	-763,22	0	dinsdag 1 januari 2019	2
700000 Omzet	PTD 02/2019	2338	2338	0	2338	vrijdag 1 maart 2019	2
602102 Consultant 3	PTD 12/2019	8250	-8250	-8250	0	zondag 1 december 2019	2
612090 Restaurantkosten	PTD 11/2019	661,7	-661,7	-661,7	0	vrijdag 1 november 2019	2
700001Omzet 1	PTD 03/2019	9091,4	9091,4	0	9091,4	vrijdag 1 maart 2019	2

Thus what we want to do with these data is see how the number of Employees influences our revenue and costs. Want to make it look like somethign like this:

Any ideas how to do this?

Thanks

[Anonymous · Accepted solution]

The solution to my problem was actually really simple in the end. I fixed part one with following formula 😅

EmployeeCount = CALCULATE(DISTINCTCOUNT(FactHarvest[NameEmployee]))

Part two after that step wass relative simple as well:

ProductivityIncomeHours = DIVIDE([totalIncome];[TotalHours])
ProductivityOutcomeHours = DIVIDE([TotalExpense];[TotalHours])

ProductivityIncomeEmployees = DIVIDE([totalIncome];[EmployeeCount])

ProductivityOutcomeEmployees = DIVIDE([TotalExpense];[EmployeeCount])

This resulted in this Graph:

Now need to figure out how I can get rid of the "false negative number" of employees count which is due to having an empty value on my months.

[Mariusz]

Hi @Anonymous 

If your fact table consists of an employee per day then you can simply create a Measure like below

Measure = COUNTROWS( FactHarvest )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

One row in the fact table is equal to one time registration. So then if I make a graph with month on x-axis and count on Y-axis I get all the rows counted for that month which is 28 to 31 days *number of employees for each day which is too much:

[Anonymous]

Anyone has any idea?

[amitchandak]

Can you share a better sample data and sample output.