Calculated Column with Var and IF Then Else

[bwasham]

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?

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler]

Does your data really look like what you posted or do you have multiple hour entries per person?

[RODRIGO_teixeir]

@Greg_Deckler, good afternoon!

I am following your solutions, but I have the following problem to calculate the total, at the row level is calculating correct, I wanted to calculate the total of the column calculated in the visual, but I can't. Follow a brief explanation of what I'm looking for, I have a base customer, I'm looking for the customer's last purchase date, to find out if it has more than 60 days, with this condition I wanted to calculate your next sell for 30 days from the date he was considered reactivated, until now after a lot of struggle, I managed, but I'm still looking for the total.

. 

 

[bwasham]

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. 

[bwasham]

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?