How to average without zero and by different category

[byWing]

Hi all,

I need help with this for a survey which I have conducted. 

I have this set of survey which I am trying to find the average hours/ people without zero:

Place	People	Category	Task	Sub-task	Hours per month
Warehouse	John	Operations	PM	PM Preperapation	5
Warehouse	John	Operations	PM	PM execution	10
Warehouse	John	Operations	CM	CM Preparation	5
Warehouse	John	Operations	CM	CM execution	10
Warehouse	John	Project	Project support	Project Plan	0
Warehouse	John	Project	Project support	Site Study	0
Warehouse	John	Project	Project support	Project execution	0
Warehouse	May	Operations	PM	PM Preperapation	3
Warehouse	May	Operations	PM	PM execution	2
Warehouse	May	Operations	CM	CM Preparation	3
Warehouse	May	Operations	CM	CM execution	5
Warehouse	May	Project	Project support	Project Plan	10
Warehouse	May	Project	Project support	Site Study	5
Warehouse	May	Project	Project support	Project execution	5
Store	Tim	Operations	PM	PM Preperapation	5
Store	Tim	Operations	PM	PM execution	6
Store	Tim	Operations	CM	CM Preparation	10
Store	Tim	Operations	CM	CM execution	5
Store	Tim	Project	Project support	Project Plan	0
Store	Tim	Project	Project support	Site Study	0
Store	Tim	Project	Project support	Project execution	0
Store	June	Operations	PM	PM Preperapation	5
Store	June	Operations	PM	PM execution	9
Store	June	Operations	CM	CM Preparation	4
Store	June	Operations	CM	CM execution	6
Store	June	Project	Project support	Project Plan	10
Store	June	Project	Project support	Site Study	5
Store	June	Project	Project support	Project execution	9

I tried using this:

Avg hours without zeros = CALCULATE(AVERAGE('Table'[Hours Per Month]),FILTER('Table','Table'[Hours Per Month]<>0))
BUT it only works if I am breaking down my visual using sub-task, but not with the category.

What I hope to get:

1. Average hours per person in each place by category, with the tasks as legend.

2. Average hours per person in each place by task, with the sub-tasks as legend.

and 3. Average must exclude the people who input 0 for that category, task or sub-task (to remove the outliners)

I am getting myself all confused after reading all the posts. Any help will be much appreciated.

Thank you so much~

[v-zhangti · Accepted solution]

Hi, @byWing 

You can try the following methods.

Measure = 
Var _table=FILTER('Table',[Hours per month]<>0)
Return
AVERAGEX(_table,[Hours per month])

Is this the result you expect? If not, please provide more sample output.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly