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,

 

Try to change the SUMX table to 

 

ALLSELECT(XXBI_MFG_RESOURCE_TXNS_V[MFG_ORDER_NAME])

 

If it doesn't work can you share a sample of your file? If there is any sensitive data send it by private message or a mockup file.

 

Regards,

MFelix

  • 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