topic Re: how to calculate total daily average of each unit head. Please help me to calculate this. in DAX Commands and Tips

how to calculate total daily average of each unit head. Please help me to calculate this.

Anjaly — Fri, 27 Jan 2023 00:03:17 GMT

I am a newbie in power Bi
unit head	employees	Days worked	Total	Daily Average
Ana	alice	2	88	44
	eliza	2	40	20
	ancy	1	4	2
	ali	3	124	62
Ami	rose	3	80	40
	rob	2	60	30
	rom	1	8	4
The answer is supposed to be like this
Unit head	employees	Days worked	Total	Daily Average	Daily_avg_manager
Ana	alice	2	88	44	32
	eliza	2	40	20
	ancy	1	4	2
	ali	3	124	62
Ami	rose	3	80	40	24.67
	rob	2	60	30
	rom	1	8	4

Re: how to calculate total daily average of each unit head. Please help me to calculate this.

FreemanZ — Fri, 27 Jan 2023 00:14:22 GMT

hi Anjaly

you can plot a table visual with the unit head column and a measure like this:

AvgByHead = DIVIDE( SUM(TableName[Total]), SUM(TableName[Days worked]) )

verified and it worked like this:

Re: how to calculate total daily average of each unit head. Please help me to calculate this.

Anjaly — Fri, 27 Jan 2023 16:51:24 GMT

Thank you so much for the solution. Total and days worked are measures . I got an error message like'column table 'total' in query 1 can not be found. Any idea how to resolve it.

Re: how to calculate total daily average of each unit head. Please help me to calculate this.

Anjaly — Fri, 27 Jan 2023 20:27:16 GMT

i got another error when slightly changed the formula

sum([Total])/sum([Days worked]). The sum function only accepts a column reference as the argumentnumber1

Re: how to calculate total daily average of each unit head. Please help me to calculate this.

Anonymous — Mon, 30 Jan 2023 08:13:36 GMT

Hi Anjaly

You can change you table like this:

Then create a measure

Measure = AVERAGE('Table'[Daily Average])

Best Regards,

Xinru Zhu

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

Re: how to calculate total daily average of each unit head. Please help me to calculate this.

Anjaly — Mon, 30 Jan 2023 20:47:58 GMT

Thank you for the response. It won't work since 'Daily Average' is a measure.

This is what I got:

Two ways I calculated. still did not correct answer

mgr_days_worked = CALCULATE([days worked1],ALL(Sheet1[unit head]))

mgr_days_worked = CALCULATE([days worked1],ALL(Sheet1[created by]))

I got correct answer for manager total

This is what I got:

mgr_total = CALCULATE([total1],ALL(Sheet1[unit head]))

actual result