Calculating percent using data from two tables

Ok, First create a Calendar Table using the following code in a new table (under Modeling in the ribbon)

 

 

Calendar Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Attendance Table'[Date] ), MAX ( 'Attendance Table'[Date] ) ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

 

 

Next create relationships between the Location field in the Employee table and the Date field in the Calendar table witht he corresponding fields in the Attendance table. The model looks like this:

Next create the measures:

 

 

Employees by Location = 
SUM('Employee Table'[Employee Count])

Employee Attendance = SUM('Attendance Table'[Daily Attendance])

 

As for the %, you need to decide which value you would like to compute.

 

What is the % of attendance for the workforce, including locations with no attendance?

 

% Attendance of workforce =
VAR _Days =
    DISTINCTCOUNT ( 'Attendance Table'[Date] )
VAR TWF =
    CALCULATE ( [Employees by Location], ALL ( 'Employee Table' ) )
VAR WF =
    IF (
        ISINSCOPE ( 'Calendar Table'[Date] ),
        [Employees by Location],
        TWF * _Days
    )
RETURN
    DIVIDE ( [Employee Attendance], WF )

What is the % of attendance of the workforce in locations with attendance only?

% Attendance by location =
VAR WF =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                'Attendance Table',
                'Calendar Table'[Date],
                'Employee Table'[Location]
            ),
            "Calc",
                CALCULATE (
                    IF ( ISBLANK ( [Employee Attendance] ), 0, [Employees by Location] )
                )
        ),
        [Calc]
    )
RETURN
    DIVIDE ( [Employee Attendance], WF )

What is the average % of attendance

Average % Attended =
AVERAGEX (
    SUMMARIZE (
        'Attendance Table',
        'Employee Table'[Location],
        'Calendar Table'[Date]
    ),
    CALCULATE ( DIVIDE ( [Employee Attendance], [Employees by Location] ) )
)

 

 

Set up the visuals using the Location field from the Employee table and the date field from the Calendar table

 

 

 

 I've attached the sample PBIX