Forum Discussion
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
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.
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.