Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you all for your time looking into my problem and solving it! I really appreciate that!
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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!
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |