topic Re: Trying to create a simple measure of Projects per Staff numbers in DAX Commands and Tips

Trying to create a simple measure of Projects per Staff numbers

StickyToffeePud — Tue, 28 Jan 2025 12:20:56 GMT

Sorry, this is first time posting. I am a self taught PowerBI dashboard creator and I am trying to create a simple 'projects per staff number' calculation for my companies dashboard. I feel it should be easy but the numbers keep being wrong, any help would be greatly appreciated, it's been quite frustrating. Details below.

The Data: I have two sources of data. A Direct Query into a SQL database (DATA1) that lists all projects, with columns for Department etc. and a spreadsheet (DATA 2) with a column for department and staff numbers.

The solution: I want to create a new measure of projects per staff numbers (PPP) and have tried creating a new measure under the spreadsheet data source with code along the lines of:

Projects per Person (PPP) =

VAR Depart_Project_Count =

COUNTAX(FILTER(DATA1[ProjectType]="PROJECT"),DATA1[Department])

VAR Staff_Count =

COUNTAX('DATA2','DATA2'[Department])

RETURN

( Depart_Project_Count / Staff_Count )

No errors are produced when I run it but gets the numbers wrong, see table belwo:

For Glasgow the number PPP should be 3.14 ish (44/14) no 22.

Any help or pointers would be greatly appreciated. Thanks

Re: Trying to create a simple measure of Projects per Staff numbers

Azadsingh — Tue, 28 Jan 2025 12:52:05 GMT

Hi StickyToffeePud , Please try below approach:

Create below measures:

Total Projects = COUNTROWS(DATA1)

Total Staff = SUM(DATA2[Staff Numbers])

Projects Per Staff =DIVIDE([Total Projects],[Total Staff],0)

Let me know if it worked for you, if not please send some sample data.

Re: Trying to create a simple measure of Projects per Staff numbers

bhanu_gautam — Tue, 28 Jan 2025 13:28:31 GMT

StickyToffeePud , Try using DAX
Projects per Person (PPP) =
VAR Depart_Project_Count =
CALCULATE(COUNTROWS(DATA1), DATA1[ProjectType] = "PROJECT")
VAR Staff_Count =
CALCULATE(SUM(DATA2[StaffNumber]))
RETURN
DIVIDE(Depart_Project_Count, Staff_Count)

Re: Trying to create a simple measure of Projects per Staff numbers

StickyToffeePud — Tue, 28 Jan 2025 14:55:38 GMT

Thanks for the quick response, but unfortunately I have just tried the above and it produced different but still incorrect results, see table below. Although weirdly, the result for Castleford is pretty close (but none of the others are)

Re: Trying to create a simple measure of Projects per Staff numbers

StickyToffeePud — Tue, 28 Jan 2025 15:23:05 GMT

Hi, Thanks for the quick response. I did try this but it just gave blanks for the answer. Here is a screen grab of Data 1:

And here is a screen grab of Data 2

Please let me know if you need anything else, thanks again.