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 can I achieve that the sum will be ok?

 

I use beneeth calculation to calculate the individual lines for standard hrs. When Basis is lot, the resource required amount should only be the minimum or maximum value (problem is that this value is presented for every single line in the database). When Basis is item, the resourse required amount should be multiplied by quantity completed.

 

HRS_STANDARD = IF(MAX(XXBI_MFG_RESOURCE_TXNS_V[BASIS])="Lot",max(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT])/60,(MAX(XXBI_WIP_JOBS_V[QUANTITY_COMPLETED])*MAX(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT]))/60)
 
 
Thanks for any help!
Need Help

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous,

     

     

    Dax measure are based on context so when you make a if statement and looking for LOT in this case the total is also calculated based on the LOT so when you are at the total you don't have the detail of LOT so the calculation is based on the second part of your measure.

     

    You need to recalculate your measure to:

     

    HRS_STANDARD =
VAR Hours_Standard =
    IF (
        MAX ( XXBI_MFG_RESOURCE_TXNS_V[BASIS] ) = "Lot",
        MAX ( XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT] ) / 60,
        (
            MAX ( XXBI_WIP_JOBS_V[QUANTITY_COMPLETED] )
                * MAX ( XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT] )
        )
            / 60
    )
RETURN
    IF (
        HASONEFILTER ( Table[MFG_ORDER_NAME] ),
        Hours_Standard,
        SUMX ( Table, Hours_Standard )
    )

    I don't have the full details of your setup but the Table on the SUMX part should be the one where you are calculating the hours.

     

    If you don't get the correct result try with one of the other tables, because this depends on the setup, if needed share a sample of the file (if any sensitive data shared it by private message).

     

    Regards,

    MFelix

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Thanks for help! I will try this next monday. But can you OR anyone explaine when to use the summarize function and when hasonefilter? I don't onderstand the logic. Also hasonefilter to me is just meant for data reacting on filters.
      • 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