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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors