Forum Discussion

BeeleJa's avatar
BeeleJa
Helper I
1 year ago
Solved

SUMX calcuation error

I am trying to calculatethe child_WO total revenue and keep ending up with the amoun for the 100-5_IRE02 being added twice where only one value should be taken for the QTY 1 (or above 0) Value I am ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    BeeleJa 

    you can try this

     

    Column = sumx(FILTER('Table','Table'[Child_WO]=EARLIER('Table'[Child_WO])&&'Table'[QTY]>0),'Table'[Revenu])
     
     
    pls see the attachment below
BeeleJa's avatar
BeeleJa
Helper I
1 year ago

Thanx for the repleis

I am not getting there - I am looking to do the following excel formula

'=SUMIFS(Revenue:Revenu;Child_WO:Child_WO;Child_WO2;QTY;">0")

 

 

  • BeeleJa's avatar
    BeeleJa
    Helper I
    1 year ago

    Some larger data set - Revenue is already a total value and does not need to be multiplied with QTY

     

    WOMAINChild_WOITEMIDQTYRevenuRevenu_Child_WO_Required_Result
    W0004613W0004613-594-90181203.42=SUMIFS(E:E;B:B;B2;D:D;">0")
    W0004613W00046133094540-11388.813964.51
    W0004613W000461391214-20013964.51
    W0004613W000461391238-20013964.51
    W0004613W000461391238-20013964.51
    W0004613W000461338-2_ER0117982.8113964.51
    W0004613W000461338-2_ER010013964.51
    W0004613W00046133616755-11376.2413964.51
    W0004613W0004613-509-900214.6113964.51
    W0004613W00046133810825-111755.9913964.51
    W0004613W00046133827241-31589.1413964.51
    W0004613W00046133883857-211795.413964.51
    W0004613W000461385057-20013964.51
    W0004613W00046133886189-1180.8313964.51
    W0004613W00046133888449-20013964.51
    W0004613W0004613-662-900616317.9213964.51
    W0004613W0004613895/7-300117.8413964.51
    W0004613W0004613895-4-3501359.8913964.51
    W0004613W0004613S3209-01320.213964.51
    W0004613W0004613S3209-21610.4113964.51
    W0004613W0004613AS4824N0612.7113964.51
    W0004613W0004613988/2-90610.2813964.51
    W0004613W0004613988-2-011121.5613964.51
    W0004613W0004613988-2-01220.413964.51
    W0004613W0004613/8-G014BC12.6313964.51
    W0004613W000461321043-3144.4813964.51
    W0004613W0004613MS21043-4135.4613964.51
    W0004613W0004613MS24622-210.1313964.51
    W0004613W0004613S24671-1410.0813964.51
    W0004613W00046135083-5BB725.713964.51
    W0004613W0004613S35769-1021.1413964.51
    W0004613W0004613S35769-1110.4113964.51
    W0004613W0004613MS9489-0610.7213964.51
    W0004613W00046139556-06159.7513964.51
    W0004613W00046139556-06159.7513964.51
    W0004613W0004613MS9556-12403613964.51
    W0004613W0004613MS9557-1299.8113964.51
    W0004613W000461301-131-9008356.1617372.75
    W0004613W000461301-131-9010656.1617372.75
    W0004613W000461301305766-11331217372.75
    W0004613W000461301352723-471693.7617372.75
    W0004613W000461301-511-90063244.817372.75
    W0004613W000461301-511-9015154.7217372.75
    W0004613W0004613013810684-2167467.0417372.75
    W0004613W0004613013810820-40017372.75
    W0004613W00046130120-4_ER0112477.0417372.75
    W0004613W00046130120-4_ER010017372.75
    W0004613W0004613013810823-313499.6317372.75
    W0004613W00046130110875-10017372.75
    W0004613W000461301S3209-13810.4717372.75
    W0004613W000461301S3209-22721.3417372.75
    W0004613W00046130121043-313944.4817372.75
    W0004613W000461301MS21043-52120.5817372.75
    W0004613W000461301S21902-6R111.8917372.75
    W0004613W000461301S24665-88160.3217372.75
    W0004613W000461301MS9557-0844.5217372.75
    W0004613W000461301MS9565-051813.517372.75
    W0004613W000461301149C0332R80.3217372.75
    W0004613W000461301149C0363R503.517372.75
    W0004613W000461301149C0432R20.1817372.75
    W0004613W000461301149C0563R211.6817372.75
    W0004613W000461301149E0663R82.3217372.75
    W0004613W00046130143DD3-7FC20.4617372.75
    W0004613W000461301AS620C10L425.8817372.75
    W0004613W000461301013827324-300749.14
    W0004613W000461301014-3_IRE011749.14749.14
    W0004613W00046130101-603-903170749.14
    W0004613W000461301023810875-100803.81
    W0004613W000461301025-1_IRE031803.81803.81
    W0004613W000461301033810949-100412.91
    W0004613W000461301039-1_IRE011412.91412.91
    W0004613W00046130103SL4-28080412.91
    W0004613W000461301043616866-1102009.61
    W0004613W000461301043810731-1102009.61
    W0004613W000461301043810948-1002009.61
    W0004613W000461301048-1_IRE0212009.612009.61
    W0004613W000461301050-5_IRE011424.522153.89
    W0004613W000461301050-5_IRE0211729.372153.89
    W0004613W000461301050-5_IRE02002153.89
    W0004613W0004613010521608-3002153.89
    W0004613W0004613010521608-3002153.89
    W0004613W0004613010521608-3002153.89
    W0004613W000461301052662-3-02502153.89
    W0004613W00046130105R2662-3-31002153.89
    W0004613W00046130105MS21060L3502153.89
    W0004613W000461301063827165-200433.21
    W0004613W000461301065-2_IRE011433.21433.21
    W0004613W00046130106-603-903210433.21
    W0004613W0004613023860865-122534.368172.26
    W0004613W0004613023863104-11572.498172.26
    W0004613W0004613023863375-14193.528172.26
    W0004613W0004613023884736-124771.28172.26
    W0004613W000461302-603-9031248.768172.26
    W0004613W000461302-518-9003125.638172.26
    W0004613W000461302AS3208-0410.118172.26
    W0004613W000461302AS3208-0710.128172.26
    W0004613W000461302AS3208-0810.158172.26
    W0004613W000461302S3209-01120.58172.26
    W0004613W000461302S3209-02210.28172.26
    W0004613W000461302S3209-10810.258172.26
    W0004613W000461302S3209-11220.38172.26
    W0004613W000461302S3209-12310.238172.26
    W0004613W000461302S3209-12510.318172.26
    W0004613W000461302S3209-14331.688172.26
    W0004613W000461302248-1-90610.128172.26
    W0004613W00046130221043-320.648172.26
    W0004613W000461302MS9489-0573.998172.26
    W0004613W000461302MS9489-0731.658172.26
    W0004613W000461302MS9489-1843.048172.26
    W0004613W000461302MS9556-0843.088172.26
    W0004613W000461302MS9556-1489.048172.26
    W0004613W000461302MS9556-2410.898172.26
    W0004613W000461303310829-5158.134143495933.64978
    W0004613W0004613033179505-11193.84563655933.64978
    W0004613W0004613033291214-2005933.64978
    W0004613W0004613034-49-7044105933.64978
    W0004613W0004613034-49-7045105933.64978
    W0004613W000461303E+49-704515658.055933.64978
    W0004613W000461303-522-9103113.925933.64978
    W0004613W000461303-504-930031.085933.64978
    W0004613W000461303-509-900421.965933.64978
    W0004613W0004613038-5-BEB-0171.75933.64978
    W0004613W000461303029/4-11020.585933.64978
    W0004613W000461303MS21043-382.565933.64978
    W0004613W00046130335275-20710.325933.64978
    W0004613W00046130335275-21410.225933.64978
    W0004613W00046130335275-21741.285933.64978
    W0004613W00046130449-Repair14237.864237.86
    W0004613W000461304-118-1300204237.86
    W0004613W000461304-503-9003404237.86
    W0004613W000461304/13-003-06004237.86
    W0004613W0004613043/5-105-91004237.86
    W0004613W00046130449-52S14N104237.86
    W0004613W0004613043368-1-8A804237.86
    W0004613W0004613043368-5-8E1604237.86
    W0004613W0004613053885057-200200.95
    W0004613W0004613057-2_IRE011200.95200.95
    • ryan_mayu's avatar
      ryan_mayu
      Super User
      1 year ago

      BeeleJa 

      you can try this

       

      Column = sumx(FILTER('Table','Table'[Child_WO]=EARLIER('Table'[Child_WO])&&'Table'[QTY]>0),'Table'[Revenu])
       
       
      pls see the attachment below