Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

sumif construction

Hi,   For each 'MFG_ORDER_NAME' and 'OP_SEQ_NUMBER' and I need to calculatie the sum of 'RESOURCE_REQUIRED_AMOUNT' and sum of transaction quantity. The last one is easy and I do not need help with ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Assume your data table(according to your screenshot)  is like

     

    If the table JOBS is like this, and there a relationship built between two tables (it is better to set cross filter direction "Both")

    ORDER_NAME qty
    1 3
    2 4
    3 5
    4 6
    5 7

     

    Then create a measure

    Measure = IF(MAX([col4])=20&&MAX([col5])="Lot",MAX([col6]),IF(MAX([col4])=30&&MAX([col5])="Item",MAX([col6])*MAX(Sheet12[qty])))

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

Assume your data table(according to your screenshot)  is like

 

If the table JOBS is like this, and there a relationship built between two tables (it is better to set cross filter direction "Both")

ORDER_NAME qty
1 3
2 4
3 5
4 6
5 7

 

Then create a measure

Measure = IF(MAX([col4])=20&&MAX([col5])="Lot",MAX([col6]),IF(MAX([col4])=30&&MAX([col5])="Item",MAX([col6])*MAX(Sheet12[qty])))

Best Regards

Maggie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Maggie. Problem solved!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Now the individual lines are correct but the sum of 'RESOURCE_REQUIRED_AMOUNT' is incorrect, which now is 13.5

      This should be 32.85. I used following calculation.

       

      Hrs_routing = IF(MAX(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_SEQUENCE_NUMBER])=20 && 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)