Distinct count and find average

[aishak12]

All,

I have two tables. I would like to create a visual that counts the total number of employees who came in on each specific day. 

For example this is my dataset. Now I want to plot a chart that shows the weekdays (MON-SUN) and on my thursday bar it should show total count of 3. 

(August 17th, 2023) - Thursday - Employee ID: 1027

(August 24th, 2023) - Thursday - Employee ID:1027

(August 31st, 2023) - Thursday - Employee ID: 1028

Table A)

- List of employees who came into office 

Table B)

- Max Capacity of seats in office 

Also I would like to divide the total numbers of employees attending (the 3 in my example) by the "HEADCOUNT". So am I able to divide these even though they are different tables? 

[BA_Pete]

Hi @aishak12 ,

I'm assuming that TableB only ever has one row per office i.e. it's a dimension table.

If so, then the following should work for you:

Create a proper calendar table and add a [DayOfWeek] column to it - there's plenty of guides online how to do this so I won't go into detail here.

Relate your calendar table to TableA on: Calendar[Date] ONE = MANY TableA[ex_date].

Relate TA and TB on: TableB[Office] ONE = MANY TableA[Office].

Create 2 measures:

_noofInWork = DISTINCTCOUNT(TableA[EmployeeID])

_noofInWork_%ofHeadcount = DIVIDE(MAX(TableB[HEADCOUNT]), [_noofInWork], 0)

In your visual, place Calendar[DayOfWeek], TableB[Office], [_noofInWork] and/or [_noofInWork_%ofHeadcount].

If your TableA contains more than seven days' worth of data, you may also need to add a [Week] column into your Calendar table so you can distinguish between different Mondays, Tuesdays etc.

Pete

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.