Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Crowdak
Regular Visitor

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

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     
      
NameEmailFloorDate_OnlyDate_TimeDistinctEntryPerDay
John Smithjsmith@contoso.com302024-10-102024-10-10 8:302024-10-10 jsmith@contoso.com
John Smithjsmith@contoso.com342024-10-102024-10-10 12:302024-10-10 jsmith@contoso.com
John Smithjsmith@contoso.com302024-10-102024-10-10 13:302024-10-10 jsmith@contoso.com
John Smithjsmith@contoso.com312024-10-112024-10-11 9:302024-10-11 jsmith@contoso.com
John Smithjsmith@contoso.com342024-10-112024-10-11 12:302024-10-11 jsmith@contoso.com
John Smithjsmith@contoso.com312024-10-112024-10-11 13:302024-10-11 jsmith@contoso.com
Bob Loblawbloblaw@contoso.com322024-10-102024-10-10 9:002024-10-10 bloblaw@contoso.com
Bob Loblawbloblaw@contoso.com342024-10-102024-10-10 12:002024-10-10 bloblaw@contoso.com
Bob Loblawbloblaw@contoso.com322024-10-102024-10-10 15:002024-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.
 
 
 
 
2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

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 

View solution in original post

Bibiano_Geraldo
Memorable Member
Memorable Member

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

I hope this helps! 
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
 Give it a like to show your appreciation!

Thank you for contributing to our amazing Power BI community! 

View solution in original post

5 REPLIES 5
v-jialongy-msft
Community Support
Community Support

Hi @Crowdak 

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

vjialongymsft_0-1731292819662.png


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:

vjialongymsft_1-1731293466954.png

vjialongymsft_2-1731293488265.png

 

 

 

 

 

 

 

 

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
Memorable Member
Memorable Member

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

I hope this helps! 
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
 Give it a like to show your appreciation!

Thank you for contributing to our amazing Power BI community! 
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Super User
Super User

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 

Thanks a lot, it worked 🙂

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.