Calculating percent using data from two tables

[Anonymous]

Hi,

I am calculating daily employee attendance, and want to create a table that has the daily employee count and a column with the percent of employees that attended the office that day. I made a visual that organizes the data by day and location, and I have another table that displays the amount of employees assigned to each location (as well as the total number of seats available in each office).

Here is some sample data: 

 

One manual method would be to group the table by location and divide the values by the corresponding employee count. So I would, for example, divide all of the Atlanta daily swipe values by 11, which is the total number of employees in that office. I would like to find a more efficient way to do this, would anyone have any advice? 

[v-shex-msft]

Hi @Anonymous,

Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[PaulDBrown]

It's important that all data shared is non confidential. Ideally we need sample from the relevant tables. You can edit any confidential fields in Excel. 
So the first sample you posted is an example of the attendance table and the last sample is the employee table? If so we can work with what you have already posted.

[Anonymous]

Here is the daily attendance sample for two days of data: 

Location	Date		Daily Attendance
LEXINGTON	2/1/2022 0:00		2
NAPA	2/1/2022 0:00		3
ATLANTA	2/1/2022 0:00		4
AVON	2/1/2022 0:00		4
CEDAR RAPIDS	2/1/2022 0:00		4
KANSAS CITY	2/1/2022 0:00		4
MIAMI	2/1/2022 0:00		4
SCOTTSDALE	2/1/2022 0:00		4
CALABASAS	2/1/2022 0:00		5
RED BANK	2/1/2022 0:00		5
CANONSBURG	2/1/2022 0:00		8
CHICAGO	2/1/2022 0:00		8
FORT WASHINGTON	2/1/2022 0:00		13
INDIANAPOLIS	2/1/2022 0:00		16
OVERLAND PARK	2/1/2022 0:00		24
NEW YORK CITY	2/1/2022 0:00		71
RED BANK	2/2/2022 0:00		3
SCOTTSDALE	2/2/2022 0:00		3
CEDAR RAPIDS	2/2/2022 0:00		4
LEXINGTON	2/2/2022 0:00		4
OVERLAND PARK	2/2/2022 0:00		5
CALABASAS	2/2/2022 0:00		6
MIAMI	2/2/2022 0:00		6
CANONSBURG	2/2/2022 0:00		8
ATLANTA	2/2/2022 0:00		9
NAPA	2/2/2022 0:00		10
AVON	2/2/2022 0:00		11
FORT WASHINGTON	2/2/2022 0:00		14
INDIANAPOLIS	2/2/2022 0:00		15
NEW YORK CITY	2/2/2022 0:00		106

This is the employee table:

Location	Employee Count
ATLANTA	11
AVON	16
CALABASAS	16
CEDAR RAPIDS	6
CHICAGO	27
FORT WASHINGTON	13
INDIANAPOLIS	62
KANSAS CITY	6
LEXINGTON	16
MINEOLA	8
NAPA	20
NEW YORK CITY	153
OVERLAND PARK	181
PITTSBURGH	7
RED BANK	6
SCOTTSDALE	7
COON RAPIDS	4
LAKE MARY	16
SAN FRANCISCO	2
VANCOUVER	8
WESTLAKE	4
MORRISTOWN	4

I can provide more if this doesn't work. Thank you again for all your help! 

[PaulDBrown · Accepted solution]

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

[Anonymous]

Hi, 

So I tried using the same methods; not sure if I am making a mistake somewhere. I keep getting this error: 

[PaulDBrown]

At what step does the error occur ?

[PaulDBrown]

Can you please post sample data or a mockup PBIX file and a depiction of the expected outcome?

[Anonymous]

Hi,

Thank you so much! This should be what I'm looking for, I am just trying to replicate it - I will upadate once I successfully do. 

One question, how were you able to place the measures/calculations under a seaparate field in the Fields section? When creating the new measurements, they automatically go under the current field I have open. 

Thanks again! 

[Anonymous]

Here is some mockup data. I would like to make a visual that would show me the percentage of employees that attended a location every single day. I am not very savvy with PowerBI, so I am not sure if what I am trying to do is possible or how I would go about it...

For example, in a table, I would like to have a drop down option for each location to display the daily attendance count for that location. 

As an example: 

Location	Date	Daily Attendance	Total Employees	Percent Attended
NYC	1/1	5	8	5/8
NYC	1/2	7	8	7/8
NYC	1/3	3	8	3/8
Houston	1/1	0	9	0/9
Houston	1/2	6	9	6/9
Houston	1/3	2	9	2/9
LA	1/1	10	10	10/10
LA	1/2	5	10	5/10
LA	1/3	8	10	8/10

I would want to have a visual in this sort of Format on PowerBI, where the rows with dates in them can be expanded to be separated by location or vice versa.  

This table above shows the attendance for each day, and I want to divide those values by the total number of employees per location, found in this table. 

I want to ultimately make a trend report that lets me see the attendance percentage every day, organized by location. 

This is what I have managed to put together, but I am still hoping to get a percentage for each office. 

[PaulDBrown]

Is the sample table of data representative of your actual tables? I'm asking because you include an image of employees by location.
Ideally we need sample data of the tables in you dataset (I would assume you at least have a table for attendance and another for employees by location, if I'm interpreting your info correctly). This will help us create the model and measures according to your actual needs.