cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NEJO Frequent Visitor
Frequent Visitor

Calculating a Sum of a Daily FTE column

Hello Experts

 

This seems like a really simple one..

 

I have a simple data set consisting of Day, Employee ID, FTE.

 

I need to be able to calculate the Average FTE for any given period (via slicers) - so could be 1 month, or could be one year, or even 1 week.  To do this - I need to first average the FTE column for that day for each employee, and then sum up the Average across the employees to give me the result I need to display in a card.

 

I think I need to create a table on the fly of average per Employee first and then sum that up - but not sure how to go about it.

 

Thanks

Neil

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Calculating a Sum of a Daily FTE column

Hello Neil,

 

Here are two possible formulas.

 

The first simply sums total FTE by day and averages the sum over the days currently filtered.

The second does the same thing but per Employee, then sums them.

 

I would expect them to return the same result, except for cases where some Employees are not present in the dataset on certain days, in which case the second formula only averages their FTE values over the days they were present, before summing.

 

Regards,

Owen

 

 

Average FTE = 
AVERAGEX ( 
    VALUES ( YourTable[Day] ),
    CALCULATE ( SUM ( YourTable[FTE] ) )
)

Average FTE per Employee Summed =
SUMX (
    VALUES ( YourTable[FTE] ),
    CALCULATE (
        AVERAGEX ( 
            VALUES ( YourTable[Day] ),
            CALCULATE ( SUM ( YourTable[FTE] ) )
        )
    )
)

 EDIT: 2nd formula corrected. SUMX changed to AVERAGEX



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
OwenAuger Super Contributor
Super Contributor

Re: Calculating a Sum of a Daily FTE column

Hello Neil

 

Here are a couple of options:

 

1. This formula will take each Day present in your dataset (subject to any filters), calculate the sum of FTE on that Day, and average over all Days.

 

Average Daily FTE =
AVERAGEX (
    VALUES ( YourTable[Day] ),
    CALCULATE ( SUM ( YourTable[FTE] ) )
)

 

 

2. If, instead, you wanted to calculate the average of each Employee's FTE over only the days for which that Employee exists in the dataset, then sum each Employee's result, you could use a slightly different formula:

 

 

Average Daily FTE Calculated Per Employee then Summed =
SUMX (
    VALUES ( YourTable[Employee] ),
    CALCULATE (
        AVERAGEX (
            VALUES ( YourTable[Day] ),
            CALCULATE ( SUM ( YourTable[FTE] ) )
        )
    )
)

 

Regards,

Owen

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




OwenAuger Super Contributor
Super Contributor

Re: Calculating a Sum of a Daily FTE column

Hello Neil,

 

Here are two possible formulas.

 

The first simply sums total FTE by day and averages the sum over the days currently filtered.

The second does the same thing but per Employee, then sums them.

 

I would expect them to return the same result, except for cases where some Employees are not present in the dataset on certain days, in which case the second formula only averages their FTE values over the days they were present, before summing.

 

Regards,

Owen

 

 

Average FTE = 
AVERAGEX ( 
    VALUES ( YourTable[Day] ),
    CALCULATE ( SUM ( YourTable[FTE] ) )
)

Average FTE per Employee Summed =
SUMX (
    VALUES ( YourTable[FTE] ),
    CALCULATE (
        AVERAGEX ( 
            VALUES ( YourTable[Day] ),
            CALCULATE ( SUM ( YourTable[FTE] ) )
        )
    )
)

 EDIT: 2nd formula corrected. SUMX changed to AVERAGEX



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User
Super User

Re: Calculating a Sum of a Daily FTE column

You said

 

”first average the fte column for that day for each employee”. Why do you need to do this for each employee?  I assume each employees only has one record for each day, so why do you need to average by employee?  Do you mean “ average the fte by day”?  

 

Then you say you want to “sum that up” but if you do that you won’t get the average, you will get the sum of the average for that month or year. So is that what you want, or do you want the average of the average?  Or something else?  Maybe you want the total fte column divided by the number of days?  It is not clear. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
NEJO Frequent Visitor
Frequent Visitor

Re: Calculating a Sum of a Daily FTE column

Apologies - I may have confused mytself a little there.

 

I have a daily table of employee and FTE on that day.

 

So if one employee was 1 FTE for 5 days, and then 0.5 FTE for Days -  then the average would be 0.75 FTE for the 10 days.  I would then want to sum up the averages (in this case the 0.75FTE) so that I get a total of the average headcount for that period. 

 

So in the above example if there were 2 employees who followed the same scenario - then the result would be a total of 1.5 FTE (2 Employees at 0.75FTE each)

 

Hope that makes sense.

 

Thanks

Neil

NEJO Frequent Visitor
Frequent Visitor

Re: Calculating a Sum of a Daily FTE column

Thanks Owen

 

The first seems to do what I need, and treats people who are not here as zero - which is what I want for this.

 

Not sure the second works though, as I end up getting a sum of everything, where I would expect to see a figure of 1.39 I think.  ABC average of 0.75 plus XYZ Average of 0.64 - totals  1.39.

 

Example dataset and output below:

 

SampleDataFTE.PNG

Thanks

Neil

Super User
Super User

Re: Calculating a Sum of a Daily FTE column

How about you post some sample data. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Gravanita Regular Visitor
Regular Visitor

Re: Calculating a Sum of a Daily FTE column

@NEJO

Is this the result you're after?

 

Capture.JPG

I ask this because this are the values you mentioned on a previous post (.75 for ABC and .64 for XYZ).

Average FTE =
AVERAGEX (
    VALUES ( Table1[TimeKeep] );
    CALCULATE ( SUMX ( ALLSELECTED ( Table1[TimeKeep] ); AVERAGE ( Table1[FTE] ) ) )
)

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 39 members 1,019 guests
Please welcome our newest community members: