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,

 

HASONEFILTER does the same HASONEVALUE so basically what the formulas does is check if there is any filter applied to the measure, in this case, when you make the line details on the MFG ORDER NAME you are applying a filter on it because you are asking for more detailed information on each line.

 

When putting it in a IF statment basically what is happenning is if MFG ORDER NAME only returns one value (has a filter) it returns true so it calculates the measure on it's own, when the result is false, on the total lines (you have more than one value on the mfg order name context) it makes the SUMX formula so picking up all the values for the HRS STANDARD per line and saving then and in the end summing all the values that were stored and giving you the correct result.

 

Regards,

MFelix

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Felix,

 

As proposed, I tried your syntax, but still the sum hours is not correct. Can you have a look?

 

Thanks!

 

  • 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