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.
I have data columns that contain total planned amount and total actual amount per date. I also have columns that contain total employees planned and actual per date. There are multiple sites / areas that have unique entries of amounts / employees, but have the same date (see data example). By dividing these two, I get a productivity per employeee amount. I then created a measure to calculate the three week moving average productivity per employee using the following formula:
Data example:
Date Area Planned Amount Actual Amount Planned Employees Employees 2017/01/01 A1 750 250 5 5 2017/01/01 B3 25 50 4 3 2017/01/01 D25 54 55 10 12 2017/01/02 B3 27 27 4 4
Productivity measure:
ThreeWeekMovingAveProductivity = AVERAGEX(DATESINPERIOD(View[Date], LASTDATE(View[Date]), -21, DAY), sum(View[DailyAmount])/sum(View[DailyEmployees]))
When I then create a second measure to determine the required amount of employees i.e:
RequiredEmployees = sum(View[PlannedAmount]) / View[ThreeWeekMovingAveProductivity]
It totals amounts for different areas or dates instead of showing me the individual required employees per date.
How can I fix this?
Hi @cornelverster,
>>By dividing these two, I get a productivity per employeee amount.
You want to calculate per employeee amount in each day? If it is, you'd better create a calculated column in actual table.
each employee amount=view[Actual Employees]/view[Employees]
>>I get a productivity per employeee amount. I then created a measure to calculate the three week moving average productivity per employee using the following formula.
RequiredEmployees=SUMX(DATEINPERIOD(view[Date],LASTDATE(view[Date],-21,DAY), view[each emplyee amount]))/COUNTX(DATEINPERIOD(view[Date],LASTDATE(view[Date],-21,DAY), vew[Date])
In addtion, in your formula, what's mean of view[DailyAmount] and view[DailyEmployees]? Could you please share more details?
Best Regards,
Angelia
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |