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

 

MBBIUser_0-1629917199172.png

 

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 IDTimekeeper NameYearMonth Fees Worked DeptFTEYRMOProd/FTE/DaySubgroup
000005Individial A20211      75,000.00ABC1202101 Construction
000005Individial A20212      75,000.00ABC1202102 Construction
000005Individial A20213      75,000.00ABC1202103 Construction
000005Individial A20214      75,000.00ABC1202104 Construction
000005Individial A20215      75,000.00ABC1202105 Construction
000005Individial A20216      75,000.00ABC1202106 Construction
000005Individial A20217      75,000.00ABC1202107 Construction
000005Individial A20201      70,000.00ABC1202001 Construction
000005Individial A20202      70,000.00ABC1202002 Construction
000005Individial A20203      70,000.00ABC1202003 Construction
000005Individial A20204      70,000.00ABC1202004 Construction
000005Individial A20205      75,000.00ABC1202005 Construction
000005Individial A20206      65,000.00ABC1202006 Construction
000005Individial A20207      60,000.00ABC1202007 Construction
000005Individial A20208      65,000.00ABC1202008 Construction
000005Individial A20209      60,000.00ABC1202009 Construction
000005Individial A202010      65,000.00ABC1202010 Construction
000005Individial A202011      60,000.00ABC1202011 Construction
000005Individial A202012      75,000.00ABC1202012 Construction

 

2 ACCEPTED SOLUTIONS
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1630288209948.png

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.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
MBBIUser
New Member

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

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1630288209948.png

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.

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.