Forum Discussion
Calculated Column with Var and IF Then Else
I would approach this as 3 measures:
August = 186 FTE1Measure = VAR __FTE = DIVIDE(SUM(Table8[Hours]),[August]) RETURN IF(__FTE>1,1,__FTE) FTEMeasure = IF(HASONEVALUE('Table8'[Staff]),[FTE1Measure],SUMX(SUMMARIZE('Table8',[Staff],"__FTE",[FTE1Measure]),[__FTE]))FTEMeasure is the one that you want to use, the other two are just intermediate.
Tha actual data has millions of rows in hours. I can make the calculations work with Group by in Power Query, but it would have to be done each month.
I am trying to create a matrix in Power BI with a column for Full Time Equivalents (FTE). The Table looks like this (Please note this is an example the actual table has well over a million lines for hours).
Staff | Hours | Aug | if(hours divided by Aug is greater then 1, 1, Hours divided by Aug) | ||
Jane | 195 | 186 | 1.00 | ||
John | 135 | 186 | 0.73 | ||
Jim | 187 | 186 | 1.00 | ||
Jason | 121 | 186 | 0.65 | ||
Jared | 180 | 186 | 0.97 | ||
Jill | 205 | 186 | 1.00 | ||
Jenny | 140 | 186 | 0.75 | ||
Sum | 6.10 |
In Excel all I would have to do is run the formula listed in the header of the table and then sum the column. Of course, because of how subtotals work in Matrixes. That’s not the result. Instead you get the total of hour divided by the total of Aug so 1163 divided by 1302 and the result is .89.
Working with Variables I have been close, and even was able to get to a point where the rows were correct and the subtotal was actual numbers. But this is not correct as it results in a total of 6.25 rather than the 6.10.
My current measure formula is this
FTEMeasure =
VAR FTE1 =
IF (
DIVIDE ( SUM ( Table1[Hours] ), SUM ( Table1[Aug] ) ) > 1,
1,
DIVIDE ( SUM ( Table1[Hours] ), SUM ( Table1[Aug] ) )
)
VAR FTESUM =
SUMMARIZE ( Table1, Table1[Hours], "FTE Sum", FTE1 )
RETURN
IF ( HASONEVALUE ( Table1[Staff] ), FTE1, SUMMARIZE ( FTESum, [FTE Sum] ) )
Which results in the .89. I know that the current if then statement after RETURN is legitimate because If I change out the Else calculation, say to 100, the result 100 shows on the subtotal line. But I just can’t get the subtotal line correct. Anyone know the answer?