Having a count of how many people came into the office and on which floor

[Crowdak]

Hello,

I am trying to get a percentage of how many people came to the office per week and on which floor they were working.

Now my issue is that my only way of tracking that is with the access card database, but people move around all day, so I might get 15 entries per person per day. 

I filter each floors on my visual with a dropdown slicer, that's not a problem. My problem is that my distinctcount per day used to randomly pick the row. And my highest percentage was the floor where the cafeteria is, but that's the floor I have the less office. So the percentage doesn't make any sense. 

My assumption is that their first entry of the day will be on the floor they'll mostly work on that day.

Access cards
Name	Email	Floor	Date_Only	Date_Time	DistinctEntryPerDay
John Smith	jsmith@contoso.com	30	2024-10-10	2024-10-10 8:30	2024-10-10 jsmith@contoso.com
John Smith	jsmith@contoso.com	34	2024-10-10	2024-10-10 12:30	2024-10-10 jsmith@contoso.com
John Smith	jsmith@contoso.com	30	2024-10-10	2024-10-10 13:30	2024-10-10 jsmith@contoso.com
John Smith	jsmith@contoso.com	31	2024-10-11	2024-10-11 9:30	2024-10-11 jsmith@contoso.com
John Smith	jsmith@contoso.com	34	2024-10-11	2024-10-11 12:30	2024-10-11 jsmith@contoso.com
John Smith	jsmith@contoso.com	31	2024-10-11	2024-10-11 13:30	2024-10-11 jsmith@contoso.com
Bob Loblaw	bloblaw@contoso.com	32	2024-10-10	2024-10-10 9:00	2024-10-10 bloblaw@contoso.com
Bob Loblaw	bloblaw@contoso.com	34	2024-10-10	2024-10-10 12:00	2024-10-10 bloblaw@contoso.com
Bob Loblaw	bloblaw@contoso.com	32	2024-10-10	2024-10-10 15:00	2024-10-10 bloblaw@contoso.com

Taux de Réservation(% Puces d'accès) = AVERAGEX(

    VALUES('Access cards'[Date_Only]),CALCULATE(DISTINCTCOUNT('Access cards'[email], FILTER('Access cards', MIN('Access cards'[Date_Time])))

    / COUNT('Roles Employés RH'[Adresse courriel au bureau]))))

Any idea how to make this work? at first I created a column that concatenated the date and email of the person, but that doesn't work.

[VahidDM · Accepted solution]

Hi @Crowdak 

To accurately calculate the percentage of employees who came to the office per week and determine on which floor they primarily worked, you can use the first entry of each person per day as their main working floor.

Solution Steps:

1. Create a Calculated Column for the First Entry Floor:

Add a calculated column in your 'Access cards' table to identify the floor where each employee made their first entry on each day.

FirstEntryFloor =

VAR FirstTime =

    CALCULATE(

        MIN('Access cards'[Date_Time]),

        FILTER(

            'Access cards',

            'Access cards'[Email] = EARLIER('Access cards'[Email]) &&

            'Access cards'[Date_Only] = EARLIER('Access cards'[Date_Only])

        )

    )

RETURN

    IF(

        'Access cards'[Date_Time] = FirstTime,

        'Access cards'[Floor],

        BLANK()

    )

• Explanation:
• The variable FirstTime calculates the earliest Date_Time for each employee on each Date_Only.
• The column FirstEntryFloor records the Floor when Date_Time equals FirstTime.
• Other entries are set to BLANK() to avoid duplication.
1. Create a Measure to Count Unique Employees per Floor per Week:

First, ensure you have a Date table linked to your 'Access cards'[Date_Only] column to handle weekly aggregation.

EmployeesPerFloor =

CALCULATE(

    DISTINCTCOUNT('Access cards'[Email]),

    'Access cards'[FirstEntryFloor] = SELECTEDVALUE('Access cards'[Floor])

)

• Explanation:
• Counts unique emails where FirstEntryFloor matches the selected floor.
• SELECTEDVALUE('Access cards'[Floor]) gets the floor selected in your slicer.
1. Calculate the Total Number of Employees:

TotalEmployees = COUNT('Roles Employés RH'[Adresse courriel au bureau])

1. Create the Percentage Measure:

PercentagePerFloor =

DIVIDE(

    [EmployeesPerFloor],

    [TotalEmployees],

    0

)

• Explanation:
• Divides the number of employees per floor by the total number of employees.
• DIVIDE function handles division by zero gracefully.
1. Set Up Your Visual:
• Slicer: Use the Floor column from 'Access cards' to filter floors.
• Axis or Columns: Use the Week Number from your Date table to display data weekly.
• Values: Add the [PercentagePerFloor] measure to show the percentage.

Important Notes:

• Date Table: Ensure you have a continuous Date table to aggregate data by weeks.
• Assumptions: This approach assumes that the first entry is indicative of the main working floor.
• Data Relationships: Verify that your tables are properly related (e.g., the Date table to 'Access cards').

Example Visualization:

• A line chart showing the [PercentagePerFloor] over weeks for each floor.
• A bar chart comparing floors for a selected week.

By following these steps, you will:

• Accurately assign each employee to a primary floor based on their first entry.
• Avoid counting multiple entries per person per day.
• Calculate meaningful percentages that reflect actual floor usage.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[v-jialongy-msft]

Hi @Crowdak 

Based on your needs, I have created the following table.

You want to calculate how many people come to the office each week, you first need a column to get the week number corresponding to the date, assuming Monday is the start of the week.

Weeknummber = WEEKNUM('Access cards'[Date_Time],2)

Then you can calculate how many people come to the office each week.

Count per week = 
var _selectweek = SELECTEDVALUE('Access cards'[Weeknummber])
RETURN
CALCULATE(DISTINCTCOUNT('Access cards'[Name]),FILTER(ALL('Access cards'),'Access cards'[Weeknummber] = _selectweek))

 Percent:

Precent = [Count per week] / COUNT('Roles Employés RH'[Adresse courriel au bureau])

You also want to know which floors they work on. Since the earliest floor employees appear on can change daily, you'll need to use a slicer to pinpoint a specific day within a specific week.

Work floor = 
VAR _selectweek = SELECTEDVALUE('Access cards'[Weeknummber])
VAR _selectdate = SELECTEDVALUE('Access cards'[Date_Only])
VAR _selectname = SELECTEDVALUE('Access cards'[Name])
VAR _mintime  = MINX(FILTER(ALL('Access cards'),'Access cards'[Weeknummber] = _selectweek && 'Access cards'[Date_Only] = _selectdate && 'Access cards'[Name] = _selectname ),'Access cards'[Date_Time])

VAR _floor = CALCULATE(SELECTEDVALUE('Access cards'[Floor]),FILTER(ALL('Access cards'), 'Access cards'[Weeknummber] = _selectweek && 'Access cards'[Date_Only] = _selectdate && 'Access cards'[Date_Time] = _mintime))

RETURN _floor

Result:

Best Regards,

Jayleny

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

[Bibiano_Geraldo · Accepted solution]

Hi,

First create a calculated column with the followind DAX

FirstEntryFloor = 
VAR FirstTime = CALCULATE(
    MIN('Access cards'[Date_Time]),
    ALLEXCEPT('Access cards', 'Access cards'[Email], 'Access cards'[Date_Only])
)
RETURN
CALCULATE(
    MAX('Access cards'[Floor]),
    'Access cards'[Date_Time] = FirstTime
)

Now create a measure to calculate the % with the following DAX

Taux de Réservation = 
VAR TotalEmployees = CALCULATE(
    DISTINCTCOUNT('Access cards'[Email]),
    ALL('Access cards')
)
VAR EmployeesPerFloor = 
    CALCULATE(
        DISTINCTCOUNT('Access cards'[Email]),
        FILTER(
            'Access cards',
            'Access cards'[FirstEntryFloor] = SELECTEDVALUE('Access cards'[Floor])
        )
    )
RETURN
DIVIDE(EmployeesPerFloor, TotalEmployees, 0)

If this answer help you, please give a Kudos and acept as solution.

Regards

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result.

[VahidDM · Accepted solution]

Hi @Crowdak 

To accurately calculate the percentage of employees who came to the office per week and determine on which floor they primarily worked, you can use the first entry of each person per day as their main working floor.

Solution Steps:

1. Create a Calculated Column for the First Entry Floor:

Add a calculated column in your 'Access cards' table to identify the floor where each employee made their first entry on each day.

FirstEntryFloor =

VAR FirstTime =

    CALCULATE(

        MIN('Access cards'[Date_Time]),

        FILTER(

            'Access cards',

            'Access cards'[Email] = EARLIER('Access cards'[Email]) &&

            'Access cards'[Date_Only] = EARLIER('Access cards'[Date_Only])

        )

    )

RETURN

    IF(

        'Access cards'[Date_Time] = FirstTime,

        'Access cards'[Floor],

        BLANK()

    )

• Explanation:
• The variable FirstTime calculates the earliest Date_Time for each employee on each Date_Only.
• The column FirstEntryFloor records the Floor when Date_Time equals FirstTime.
• Other entries are set to BLANK() to avoid duplication.
1. Create a Measure to Count Unique Employees per Floor per Week:

First, ensure you have a Date table linked to your 'Access cards'[Date_Only] column to handle weekly aggregation.

EmployeesPerFloor =

CALCULATE(

    DISTINCTCOUNT('Access cards'[Email]),

    'Access cards'[FirstEntryFloor] = SELECTEDVALUE('Access cards'[Floor])

)

• Explanation:
• Counts unique emails where FirstEntryFloor matches the selected floor.
• SELECTEDVALUE('Access cards'[Floor]) gets the floor selected in your slicer.
1. Calculate the Total Number of Employees:

TotalEmployees = COUNT('Roles Employés RH'[Adresse courriel au bureau])

1. Create the Percentage Measure:

PercentagePerFloor =

DIVIDE(

    [EmployeesPerFloor],

    [TotalEmployees],

    0

)

• Explanation:
• Divides the number of employees per floor by the total number of employees.
• DIVIDE function handles division by zero gracefully.
1. Set Up Your Visual:
• Slicer: Use the Floor column from 'Access cards' to filter floors.
• Axis or Columns: Use the Week Number from your Date table to display data weekly.
• Values: Add the [PercentagePerFloor] measure to show the percentage.

Important Notes:

• Date Table: Ensure you have a continuous Date table to aggregate data by weeks.
• Assumptions: This approach assumes that the first entry is indicative of the main working floor.
• Data Relationships: Verify that your tables are properly related (e.g., the Date table to 'Access cards').

Example Visualization:

• A line chart showing the [PercentagePerFloor] over weeks for each floor.
• A bar chart comparing floors for a selected week.

By following these steps, you will:

• Accurately assign each employee to a primary floor based on their first entry.
• Avoid counting multiple entries per person per day.
• Calculate meaningful percentages that reflect actual floor usage.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[Crowdak]

Thanks a lot, it worked 🙂