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?
Solved! Go to 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.
Proud to be a Super User!
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?
Does your data really look like what you posted or do you have multiple hour entries per person?
Proud to be a Super User!
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 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.
Proud to be a Super User!
@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.
.
User | Count |
---|---|
413 | |
93 | |
73 | |
60 | |
56 |
User | Count |
---|---|
396 | |
102 | |
78 | |
70 | |
49 |