Measure or New Column?

[A-Aron]

Hey Pros, im new to BI so here's my dilemma,

I have a sharepoint connection with several excel sheets of the same type that i have combined to make a single query. Every different sheet is a different site used by a differnet amount of users that log their daily hours. I am being asked to create an efficiency percentage per site that takes the logged hours against the potential hours that could be worked. Each site logs multiple jobs per day so i need to figure out how to make a DAX measure that will consider the site in one column then take a specified number (static number of hours) divided by how many entries per day. If anyone can understand what im trying to say any help would be awesome. heres an example table if it helps. 

Date	Site	Hours	Potential hours
1/1	Neosho	2	=if(site=neosho,14)/COUNT,date)
1/1	Nesosho	4
1/2	Brim	5
1/2	Brim	3

[A-Aron]

Hello thank you for replying!! So the number is different for each site becasue each site has diferent number of employees. So for neosho its 14 becasue they have 2. theres Brimfield which as 2, Newville has 3, Victorville has 2 Fontana has one and Gastonia has 2. I was just going to create a macro in the excel that just logs a 14 or whichever then append it to the query but i was hoping there is a cleaner way. 

[tamerj1 · Accepted solution]

@A-Aron 
Add a small lookup (dimintion) table that contains two columns: Site and # of Employees. A relationship can then be created between the two tables via [Site] columns. In your visula silce by the Site column from the lookup table and the measure would be
DIVIDE ( SELECTEDVALUE ( LookupTable[# of Employees] ), COUNTROWS ( FactTable ) )

[A-Aron]

This is gold thank you !!!!!!!

[tamerj1]

Hi @A-Aron 

my understanding that for Neosho the result would be 14/2 = 7. What about other sites? Do you have a list that contains the sites vs this constant number or it is the same number across all sites?