Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anjaly
Frequent Visitor

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

I am a newbie in power Bi   
unit heademployeesDays workedTotalDaily Average 
Anaalice28844 
 eliza24020 
 ancy142 
 ali312462 
Amirose38040 
 rob26030 
 rom184 
The answer is supposed to be like this  
Unit heademployeesDays workedTotalDaily AverageDaily_avg_manager
Anaalice2884432
 eliza24020 
 ancy142 
 ali312462 
Amirose3804024.67
 rob26030 
 rom184 
5 REPLIES 5
v-xinruzhu-msft
Community Support
Community Support

Hi @Anjaly 

You can change you table like this:

vxinruzhumsft_0-1675066347098.png

 

Then create a measure 

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

vxinruzhumsft_1-1675066382849.png

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.

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]))
Anjaly_2-1675111398641.png

actual result

Anjaly_3-1675111478154.png

 

Anjaly
Frequent Visitor

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.

Anjaly
Frequent Visitor

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

FreemanZ
Super User
Super User

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:

FreemanZ_0-1674778446367.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.