topic Need help to calculate average count of the column based on week type column in DAX Commands and Tips

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

Mohivaj — Mon, 19 Feb 2024 17:26:24 GMT

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

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

Greg_Deckler — Mon, 19 Feb 2024 17:34:41 GMT

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 ?

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

Mohivaj — Mon, 19 Feb 2024 17:39:55 GMT

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

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

Greg_Deckler — Mon, 19 Feb 2024 21:06:58 GMT

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

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

Mohivaj — Tue, 20 Feb 2024 00:18:28 GMT

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

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

Anonymous — Tue, 20 Feb 2024 08:06:58 GMT

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.

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

Greg_Deckler — Tue, 20 Feb 2024 13:32:44 GMT

Mohivaj What I provided should work then.

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

Mohivaj — Wed, 21 Feb 2024 07:17:57 GMT

Thanks Greg_Deckler Anonymous