Aggregation not correct

[Hyperchef1969]

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!

[MFelix]

Hi @Hyperchef1969,

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

[Hyperchef1969]

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]

Hi @Hyperchef1969,

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

[Hyperchef1969]

Hi Felix,

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

Thanks!

[MFelix]

Hi @Hyperchef1969,

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

[Hyperchef1969]

Felix, you van download the file:
https://drive.google.com/open?id=13X8wROZ0tyUlwI3uahNAZK_Y0u7EtVSy

[MFelix]

Hi @Hyperchef1969,

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

[Hyperchef1969]

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]

Hi @Hyperchef1969,

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

[Hyperchef1969]

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!

[Hyperchef1969]

Hi Felix,

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

Regards.