Need help to calculate average count of the column based on week type column

[Mohivaj]

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

[Greg_Deckler · Accepted 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

[Mohivaj]

Thanks @Greg_Deckler @v-nuoc-msft 

[v-nuoc-msft · Accepted solution]

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.

[Greg_Deckler]

@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 ?

[Mohivaj]

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 

[Greg_Deckler · Accepted 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

[Mohivaj]

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

[Greg_Deckler]

@Mohivaj What I provided should work then.