How to calculate the correct time per order on order level and employee level?

[jordy233]

I have a question about how to calculate the correct time per order on order level and employee level.

Multiple employees could work on one order.

In the example below, employee 1 has a total time of 16,85 on order B, and Employee 2 has worked 8,47 on order B. The sum of this order is 25,32 (column ‘Sum of order’). In DAX I created the following measure:

Sum of order =

    CALCULATE(

        SUM('table 1'[Total time per order]),

        ALL(table 2[employee_fullname]),

        VALUES(table 3[OrderNr]))

However, the total amount is incorrect when filtering on employee. In the example below, I’ve shown two overviews. Overview 1: no filter on employee. The total of column Amount is calculated correctly. Overview 2: a filter on employee (show only employee 1). The total of column Amount is calculated incorrectly.

Did I make a mistake in the DAX measure? Of how can I solve this issue?

[amitchandak]

@jordy233 , Ideally inthis case simple sum should work

Sum of order =

Sumx( VALUES(table 3[OrderNr]), calculate(SUM('table 1'[Total time per order]) ) )

[jordy233]

Thanks for your reply. However, this is not the solution. The calculated total amount is 2517,75 in stead of 1872,21.

It includes the total time of employee 2 as well. 

Have you got other ideas?

[v-stephen-msft]

Hi @jordy233 ,

How was your Amount formula written?

It is very helpful for me to share your pbix file with me.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.