Calculate Production per FTE per Day

[MBBIUser]

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

[v-yalanwu-msft · Accepted solution]

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.

[MBBIUser]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yalanwu-msft · Accepted solution]

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.

[MBBIUser]

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!

[lbendlin]

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