cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5

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

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

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. 

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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.
. 

Captura de Tela (40).pngCaptura de Tela (41).pngCaptura de Tela (42).png 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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