Reply
Frequent Visitor
Posts: 10
Registered: ‎08-28-2018
Accepted Solution

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


Accepted Solutions
Super User
Posts: 626
Registered: ‎02-29-2016

Re: Calculating a Sum of a Daily FTE column

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 626
Registered: ‎02-29-2016

Re: Calculating a Sum of a Daily FTE column

[ Edited ]

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

 

Super User
Posts: 626
Registered: ‎02-29-2016

Re: Calculating a Sum of a Daily FTE column

[ Edited ]

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

Super User
Posts: 1,627
Registered: ‎07-03-2015

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.
Frequent Visitor
Posts: 10
Registered: ‎08-28-2018

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

Frequent Visitor
Posts: 10
Registered: ‎08-28-2018

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
Posts: 1,627
Registered: ‎07-03-2015

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
Regular Visitor
Posts: 39
Registered: ‎07-09-2017

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