cancel
Showing results for
Did you mean:
New Member

Calculate Production per FTE per Day

Hello,

I am struggling with creating a dax measure to calculate production per FTE per day. Any help will be greatly appreciated.

It has to be a measure that aggregates so that I can use slicer to drill into details.

I have tried below as a calculated column but it didn't work. It has to be a measure that aggregates.

Below is a sample of my data.

 YRMO # of Days 202101 20 202102 20 202103 23 202104 22 202105 20 202106 22 202107 21 202108 22 202109 21 202110 21 202111 20 202112 20 202001 22 202002 20 202003 22 202004 22 202005 20 202006 22 202007 22 202008 21 202009 21 202010 22 202011 19 202012 21

 Timekeeper ID Timekeeper Name Year Month Fees Worked Dept FTE YRMO Prod/FTE/Day Subgroup 000005 Individial A 2021 1 75,000.00 ABC 1 202101 Construction 000005 Individial A 2021 2 75,000.00 ABC 1 202102 Construction 000005 Individial A 2021 3 75,000.00 ABC 1 202103 Construction 000005 Individial A 2021 4 75,000.00 ABC 1 202104 Construction 000005 Individial A 2021 5 75,000.00 ABC 1 202105 Construction 000005 Individial A 2021 6 75,000.00 ABC 1 202106 Construction 000005 Individial A 2021 7 75,000.00 ABC 1 202107 Construction 000005 Individial A 2020 1 70,000.00 ABC 1 202001 Construction 000005 Individial A 2020 2 70,000.00 ABC 1 202002 Construction 000005 Individial A 2020 3 70,000.00 ABC 1 202003 Construction 000005 Individial A 2020 4 70,000.00 ABC 1 202004 Construction 000005 Individial A 2020 5 75,000.00 ABC 1 202005 Construction 000005 Individial A 2020 6 65,000.00 ABC 1 202006 Construction 000005 Individial A 2020 7 60,000.00 ABC 1 202007 Construction 000005 Individial A 2020 8 65,000.00 ABC 1 202008 Construction 000005 Individial A 2020 9 60,000.00 ABC 1 202009 Construction 000005 Individial A 2020 10 65,000.00 ABC 1 202010 Construction 000005 Individial A 2020 11 60,000.00 ABC 1 202011 Construction 000005 Individial A 2020 12 75,000.00 ABC 1 202012 Construction

2 ACCEPTED SOLUTIONS
Community Support

Hi, @MBBIUser ;

You could try to create a measure :

``````Measure =
DIVIDE (
DIVIDE ( SUM ( [ Fees Worked ] ), SUM ( [FTE] ) ),
LOOKUPVALUE ( 'Sheet'[# of Days], Sheet[YRMO], MAX ( 'Product'[YRMO] ) ))``````

Or

``````Measure2 =
DIVIDE (
CALCULATE (
DIVIDE ( SUM ( [ Fees Worked ] ), SUM ( [FTE] ) ),
ALLSELECTED ( 'Product' )),
LOOKUPVALUE ( 'Sheet'[# of Days], Sheet[YRMO], MAX ( 'Product'[YRMO] ) ))
``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
New Member

Thank you all for your time looking into my problem and solving it! I really appreciate that!

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi, @MBBIUser ;

You could try to create a measure :

``````Measure =
DIVIDE (
DIVIDE ( SUM ( [ Fees Worked ] ), SUM ( [FTE] ) ),
LOOKUPVALUE ( 'Sheet'[# of Days], Sheet[YRMO], MAX ( 'Product'[YRMO] ) ))``````

Or

``````Measure2 =
DIVIDE (
CALCULATE (
DIVIDE ( SUM ( [ Fees Worked ] ), SUM ( [FTE] ) ),
ALLSELECTED ( 'Product' )),
LOOKUPVALUE ( 'Sheet'[# of Days], Sheet[YRMO], MAX ( 'Product'[YRMO] ) ))
``````

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

This is great!! I actually was pretty close but I was missing that MAX piece in the lookupvalue section. Thank you so very much for your help!

Super User

@MBBIUser Doesn't look like your tables are connected. In your data model they should connect via the YRMO field, with the Day number reference table as a dimension pointing to the other (fact) table.

After that the measure becomes a single DIVIDE statement and it will allow all your desired aggregations.

Announcements

Launching new user group features

Learn how to create your own user groups today!