Trying to create a simple measure of Projects per Staff numbers

[StickyToffeePud]

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

[bhanu_gautam]

@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)

[StickyToffeePud]

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)

[Azadsingh]

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.

[StickyToffeePud]

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.