Why do my visualizations only show total amounts when I divide by a measure?

[cornelverster]

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?

[v-huizhn-msft]

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