Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Martin2224
New Member

FTE calculation

Hi all,

 

I am looking for a DAX statement which returns the number of FTE given the context. For one month only, I have a solution (*), but for periods longer than 1 month I would like the measure to return the weighted average of FTE, so total hours worked / total workable hours in the selected months. The problem is level of aggregation I think, as the table contains one column for workable hours which only relates to month, while the hours worked relate to employee, type and category.

 

(*)  SUMX( FILTER( Table ; [Type] = "Hours" ) ; Table[Amount] / Table[Workable hours] )

 

Martin2224_1-1678456238155.png

 

Your help is highly appreciated!

 

Regards,

Martin

1 ACCEPTED SOLUTION

@Martin2224 

 

Your last post was extremely helpful.

Thank you very much for the expected results.

 

So you can combine these measures into one measure using variables.  However, I've left them separate to show step by step.  This is especially useful when testing pieces of DAX within the table visual results.

 

Step 1) Create a measure to get the Billable Hours

WinterMist_0-1678464827182.png

 

Step 2) Create a measure to get the Workable Hours

WinterMist_1-1678464859301.png

 

Step 3) Create a measure to Divide. 

Billable Hours / Workable Hours

WinterMist_2-1678464891598.png

 

Results:

WinterMist_3-1678464949821.png

 

WinterMist_4-1678465294214.png

 

Regards,

Nathan

View solution in original post

6 REPLIES 6
Martin2224
New Member

This seems to work properly! Thanks Nathan!

WinterMist
Impactful Individual
Impactful Individual

@Martin2224 

 

Can you provide a mock visual example with the expected results (corresponding with your original table visual), which shows the period is longer than 1 month?

 

As you stated, the table visual originally provided has no row which is longer than 1 month, so the existing table visual does not satisfy the need.

 

Regards,

Nathan

@WinterMist , thanks for your quick response. I think my question is somewhat confusing. With "for periods longer than 1 month I would like the measure to return the weighted average of FTE" I meant, that when I would select multiple months in a report, the measure would return the weighted average of these months, rather than the sum of FTE's over the months.

 

@Martin2224 

 

Thanks for clarifying.  A couple more q's though.

 

When you "select multiple months in the report", do you mean that you are selecting months from a slicer?  Or do you simply mean when multiple months are included in the filter context of the visual (like the screenshot shows)?

 

Would it be possible then to add to the existing visual (perhaps in Excel) a mock-up of what the expected values would be for this visual, when multiple months are selected?

 

Thanks,

Nathan

@WinterMist the table presented earlier is my source data. The output of the measure should return the highlighted values below. With the current DAX statement I get wrong FTE totals.

 

Martin2224_0-1678462540771.png

So the formula needs first needs to sum the hours in the filtercontext (i.e. Jan-Feb). For Employee A, billable hours, this is 160+160 =320 hours. Next, the measure needs to divide the sum by 176+160=336 workable hours for the month. 

 

And also, for example, for total billable hours Jan (160+172+120) + Feb (160+148+152) = 912, the sum needs to be divided by 176+160=336 workable hours.

@Martin2224 

 

Your last post was extremely helpful.

Thank you very much for the expected results.

 

So you can combine these measures into one measure using variables.  However, I've left them separate to show step by step.  This is especially useful when testing pieces of DAX within the table visual results.

 

Step 1) Create a measure to get the Billable Hours

WinterMist_0-1678464827182.png

 

Step 2) Create a measure to get the Workable Hours

WinterMist_1-1678464859301.png

 

Step 3) Create a measure to Divide. 

Billable Hours / Workable Hours

WinterMist_2-1678464891598.png

 

Results:

WinterMist_3-1678464949821.png

 

WinterMist_4-1678465294214.png

 

Regards,

Nathan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors