Calculating a Sum of a Daily FTE column

[NEJO]

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

[MattAllington]

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. 

[NEJO]

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

[MattAllington]

How about you post some sample data. 

[OwenAuger · Accepted solution]

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

[NEJO]

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:

Thanks

Neil

[Anonymous]

@NEJO

Is this the result you're after?

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] ) ) )
)

[OwenAuger]

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