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,

 

 

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

    • 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