Bar Chart Not Showing Correct Employee Counts by WFH Days

[UdhayakumarT]

Hi Power BI Community,

I’m trying to create a bar chart in Power BI to group employees based on their total Work-From-Home (WFH) days into specific ranges ("0 - 2", "3 - 5", "6 - 10", "10+"). The X-axis should show these ranges, and the Y-axis should show how many employees fall into each range. I want the chart to update correctly when I filter by month or year. I’ve followed some steps, but the chart isn’t giving me the results I expect. Can someone please help me fix this?

My Data

I have a table named WFH_Data with these columns:

• ID: A number that identifies each employee (e.g., 1, 2, 3).
• Name: The employee’s name (e.g., AA, BB, CC).
• Start Date: The first day of the WFH period.
• End Date: The last day of the WFH period.
• Old No of Days: The total number of days between Start Date and End Date (not used for my calculation).
• Date List: Date List is extracted from Start Date and End Date applied by employees.
• No of Days New: The number of WFH days for that date (e.g., 1 for a full day, 0.5 for a half day).

 Here’s a sample of my data:

What I Want

I need a bar chart where:

• X-Axis: Shows the WFH day ranges: "0 - 2", "3 - 5", "6 - 10", "10+".
• Y-Axis: Shows the number of employees in each range.
• How to Group Employees:
  • "0 - 2": Total WFH days are 2 or fewer.
  • "3 - 5": Total WFH days are more than 2 but 5 or fewer.
  • "6 - 10": Total WFH days are more than 5 but 10 or fewer.
  • "10+": Total WFH days are more than 10.
• The total WFH days for each employee is the sum of No of Days New for all their dates.
• The chart should change when I filter by month or year using the Date List column.

What I Expect

Based on the sample data:

• Total WFH Days per Employee:
  • ID 1 (AA): 1 + 1                     = 2 days    → "0 - 2"
  • ID 2 (BB): 1 + 1 + 1 + 1         = 4 days    → "3 - 5"
  • ID 3 (CC): 0.5                         = 0.5 days  → "0 - 2"
  • ID 4 (DD): 1 + 1 + 1 + 1 + 1 = 5 days     → "3 - 5"
  • ID 5 (EE): 1+1+1+1+1+1+1  = 7 days     → "6 - 10"
• Bar Chart (without filter):
  • "0 - 2": 2 employees (ID 1, ID 3)
  • "3 - 5": 2 employees (ID 2, ID 4)
  • "6 - 10": 1 employees (ID 5)
  • "10+": 0 employees

• Bar Chart (filtered to January 2025):
  • ID 5 (EE) has only 2 days in January (30-01-2025 and 31-01-2025) → "0 - 2"
  • "0 - 2"  : 3 employees (ID 1: 2 days, ID 3: 0.5 days, ID 5: 2 days)
  • "3 - 5"  : 2 employees (ID 2: 4 days, ID 4: 5 days)
  • "5 - 10": 0 employees
  • "10+"   : 0 employees

• If I (filtered to Feb 2025):
  • ID 5 (EE) has 5 days in Feb (01-02-2025 and 05-02-2025) → "3 - 5"
  • "0 - 2"  : 0 employees 
  • "3 - 5"  : 1 employees (ID 5: 5 days)
  • "5 - 10": 0 employees
  • "10+"   : 0 employees

Now I am Getting:

Without Filter		Filtering Jan Month		Filtering Feb Month
Bucket Range	Employee Count		Bucket Range	Employee Count		Bucket Range	Employee Count
0-2	2		0-2	2		0-2	0
3 - 5	2		3 - 5	2		3 - 5	0
6 - 10	1		6 - 10	1		6 - 10	1
10+	0		10+	0		10+	0

I Want like this:

Without Filter		Filtering Jan Month		Filtering Feb Month
Bucket Range	Employee Count		Bucket Range	Employee Count		Bucket Range	Employee Count
0-2	2		0-2	3		0-2	0
3 - 5	2		3 - 5	2		3 - 5	1
6 - 10	1		6 - 10	0		6 - 10	0
10+	0		10+	0		10+	0

[Abhilash_P · Accepted solution]

Hi @UdhayakumarT,

Below are the steps 
1. First create summarize table with group by columns ID, Datelist, No of Days New
2. Create Measure to define the bucket list

3. Create static bucketlist tables as below

4. COUNTX(FILTER(VALUES('Bucket List'[emp_id]),[Sum of approved days] >= MIN('Bucket List Table'[Startlimit])&& [Sum of approved days] < MAX('Bucket List Table'[Endlimit])),'Bucket List'[emp_id])

Please use below video  from @amitchandak as reference
https://youtu.be/CuczXPj0N-k?si=W_mnUC3YydNcWEx0

[Anonymous]

Hi @UdhayakumarT,

Thanks for reaching out to the Microsoft fabric community forum.

The issue arises because the chart is not grouping employees based on their WFH totals within the selected time frame. Instead, it’s calculating total WFH days without properly applying the month/year filters, or it's aggregating across the entire dataset rather than per employee in the current filter context.

The overall thing you need to do to solve the issue is to calculate total WFH days per employee after filters are applied (e.g., January 2025) then classify each employee into a bucket (like “0 - 2”, “3 - 5”, etc.), then count how many employees fall into each bucket. Make sure this logic updates dynamically when slicers (like month/year) are used.

To create a new calculated table use this:  

Employee Buckets =
VAR Employees =
SUMMARIZE(
WFH_Data,
WFH_Data[ID],
WFH_Data[Name],
"TotalWFH", CALCULATE(SUM(WFH_Data[No of Days New]))
)
RETURN
ADDCOLUMNS(
Employees,
"Bucket",
SWITCH(
TRUE(),
[TotalWFH] <= 2, "0 - 2",
[TotalWFH] <= 5, "3 - 5",
[TotalWFH] <= 10, "6 - 10",
"10+"
)
)

This table will update based on any slicer/filter applied to Date List. Now use the nbelow to create a measure to count employees per bucket - "Employee Count = COUNTROWS('Employee Buckets')"

Now to create a bar chart in X-Axis use "bucket" from the Employee Bucket table and in Y-Axis use the Employee Count measure. Then add slicers for month/year based on your Date List column or a proper Date table, if you have one.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.