cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated Column with Var and IF Then Else

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?

1 ACCEPTED SOLUTION

Accepted Solutions Super User IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

5 REPLIES 5
Frequent Visitor

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? Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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. Super User IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor
```@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.```
`. `    Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella. Top Solution Authors
Top Kudoed Authors
Users online (2,775)