count with criteria then divide based on criteria.

[A-Aron]

Hello,

Im new to DAX and am having trouble getting a measure to work out in my head.  

I need a measure or column that is potential hours worked per day per site. Each site has a different number of employees that could get 7 hours per day per employee. The table adds a row for every work entry in excel per day (multiple). The way i was going to get the potential hours per day per site is to count the number of entries that are the same day then if the site is call x then divide it by however many people work at the site. Is this possible to combine count, divide and if to get the desired result? Any guidance would be greatly appreciated below is a shot at it what im kind of trying to do. the 14 is how many hours brim should be able to get because htey have 2 people.

Potential Hours = DIVIDE(IF('Quality BI SharePoint folder files'[Location]='Brimfield',COUNT('Quality BI SharePoint folder files'[Date]),14))

[A-Aron]

count of date, divided by a specified number determined by if statement criteria

[v-zhangti · Accepted solution]

Hi, @A-Aron 

Your equation can be rewritten as follows:

Column =
VAR N1 =
    CALCULATE (
        COUNT ( 'Quality BI SharePoint folder files'[Date] ),
        FILTER ( 'Quality BI SharePoint folder files', [Location] = "Brimfield" )
    )
RETURN
    DIVIDE ( N1, 14 )

If this does not solve your problem, please provide more sample data and the output you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.