Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
This is my example table which already has the weektype. Employees are in shift on different week type. I want to calulate the average of employees baesd on weektype (weekend/weekday)
Employee | weektype | date | team |
AAA | weekend | Saturday | A |
AAA | weekday | Monday | A |
BBB | weekday | Tuesday | B |
AAA | weekday | Wednesday | A |
CCC | weekday | Thursday | C |
Solved! Go to Solution.
@Mohivaj I still don't know if I understand what you are asking for, an average over what? But, maybe you are looking for something like this:
Average Weekday =
VAR __Table = SUMMARIZE( FILTER('Table', [weektype] = "weekday"), [date], "__Count", COUNTROWS('Table') )
VAR __Result = AVERAGEX( __Table, [__Count])
RETURN
__Result
Hi @Mohivaj
@Greg_Deckler Thank you for sharing !
For your question, here is the method I provided:
Here's some dummy data
"Table"
Create a measure. Calculate the average number of employees working on weekdays and weekends for different teams.
Average Employee =
var _week =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[weektype] = MAX('Table'[weektype])
&&
'Table'[team] = MAX('Table'[team])
)
)
var _total =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[team] = MAX('Table'[team])
)
)
RETURN _week / _total
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Mohivaj
@Greg_Deckler Thank you for sharing !
For your question, here is the method I provided:
Here's some dummy data
"Table"
Create a measure. Calculate the average number of employees working on weekdays and weekends for different teams.
Average Employee =
var _week =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[weektype] = MAX('Table'[weektype])
&&
'Table'[team] = MAX('Table'[team])
)
)
var _total =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[team] = MAX('Table'[team])
)
)
RETURN _week / _total
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mohivaj So, what is the output you are looking for with that sample data and why? I mean, is it average employees per day per week type or by team per week type or ?
Thanks for the response @Greg_Deckler .
In the power BI visualization
I wanted to show the weekend/weekday avg employee count in a bar chart so that we can find weekend or weekday employee count is more
@Mohivaj I still don't know if I understand what you are asking for, an average over what? But, maybe you are looking for something like this:
Average Weekday =
VAR __Table = SUMMARIZE( FILTER('Table', [weektype] = "weekday"), [date], "__Count", COUNTROWS('Table') )
VAR __Result = AVERAGEX( __Table, [__Count])
RETURN
__Result
Hi @Greg_Deckler I wanted to visualize the average count of employees working on a weekday vs weekend for a team . If I choose team A I should be able to see the average count of employees working on a weekday and weekend
@Mohivaj What I provided should work then.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
14 | |
12 | |
9 |