Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Aggregation not correct

Hi,   For every workorder (MFG_ORDER_NAME), I want to calculate the standard HOURS per operation seq and resource seq. The line calculation is oke, however the sum of HRS_STANDARD is not ok. How ca...
Need Help
MFelix's avatar
MFelix
Super User
7 years ago

Hi Anonymous,

 

Since measure are based on context sometimes using variables within the measures changes the context, create a measure for hour standard and then a sumx of those check measure below:

 

Hour Standard =
IF (
    MAX ( Resource_transactions[BASIS] ) = "Lot";
    MAX ( Resource_transactions[RESOURCE_REQUIRED_AMOUNT] ) / 60;
    (
        MAX ( Job_quantity[Job_qy] )
            * MAX ( Resource_transactions[RESOURCE_REQUIRED_AMOUNT] )
    )
        / 60
)



HRS_STANDARD = SUMX(Resource_transactions;[Hour Standard])

In the attach PBIX file I have added both to the table visual but you only need to addthe last one.

 

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Felix,

     

    Thanks for your help, but the line and sum result of HRS_STANDARD is not correct. The calculation details of 'Hour standard' are correct, however the sum is incorrect. See also my remarks in the Power BI file.

     

    Can you have a look?

     

    Thanks in advance.

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous,

     

    Not really sure if I understand your needs.

     

    When you have a LOT you should only consider one line if it's items you should consider all the lines for the SUMX?

     

    Regards,

    MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Felix,

     

    That is true. When 'Basis' is Lot then I need only need one value/line per OP_seq & RES_seq. If 'Basis' is Item then I need the req_amount * qty completed per OP_seq & RES_seq (which is one line). Finally I need the sum of these elements which will be the req_amount for the complete workorder.

     

    Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Felix,

     

    Can you have a look at my last reply? Honestly I need the solution urgently.

     

    Regards.