Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
BeeleJa
Helper I
Helper I

SUMX calcuation error

‎05-05-2025 09:35 AM

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 getting is 3883.26 where 2153.89 was expected

 

Any thoughts on an approach

 

WOMAINChild_WOITEMIDQTYRevenueRevenue - Child_WO
W0004613W00046130105100-5_IRE011424.523883.26
W0004613W00046130105100-5_IRE0211729.373883.26
W0004613W00046130105100-5_IRE02003883.26
W0004613W00046130105608-3003883.26
W0004613W00046130105608-3003883.26
W0004613W00046130105608-3003883.26
W0004613W000461301053-02503883.26
W0004613W000461301053-31003883.26
      
Labels:
Message 1 of 12
511 Views
0
1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User
In response to BeeleJa

‎05-07-2025 02:21 AM

@BeeleJa 

you can try this

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

SUMX calcuation error.pbix
Message 7 of 12
383 Views
0
11 REPLIES 11
BeeleJa
Helper I
Helper I

‎05-07-2025 07:21 AM

Hello Ryan

Somehow the formula provided is not working in myh full live version

specifically for the child_WO W00046130105 It is still adding another 1729.37

I expect in relation to the item 0-5_IRE02 that is there twice, once with the QTY zero and once with the QTY 1

 

Any thoughts on how to improve the formula in another way, for example with using 'VAR' declarations ?

 

Message 8 of 12
366 Views
0
v-saisrao-msft
Community Support
Community Support
In response to BeeleJa

‎05-14-2025 08:25 PM

Hi @BeeleJa,

 

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

 

Thank you.

Message 12 of 12
260 Views
0
v-saisrao-msft
Community Support
Community Support
In response to BeeleJa

‎05-09-2025 02:56 AM

Hi @BeeleJa,
I wanted to check if you had the opportunity to review the information provided by @ryan_mayu . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Message 11 of 12
301 Views
0
ryan_mayu
Super User
Super User
In response to BeeleJa

‎05-07-2025 05:39 PM

@BeeleJa 

Is this part that what you are mentioning?

 

11.png

 

I checked your sample data. It's the same as what you expected.

 

pls update the sample data and expected output.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 9 of 12
346 Views
0
v-saisrao-msft
Community Support
Community Support
In response to ryan_mayu

‎05-11-2025 09:22 PM

Hi @BeeleJa,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Message 10 of 12
283 Views
0
BeeleJa
Helper I
Helper I

‎05-06-2025 07:31 AM

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")

 

 

Message 5 of 12
408 Views
0
BeeleJa
Helper I
Helper I
In response to BeeleJa

‎05-06-2025 07:35 AM

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
Message 6 of 12
408 Views
0
ryan_mayu
Super User
Super User
In response to BeeleJa

‎05-07-2025 02:21 AM

@BeeleJa 

you can try this

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




SUMX calcuation error.pbix
Message 7 of 12
384 Views
0
Jihwan_Kim
Super User
Super User

‎05-05-2025 09:55 AM

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1746464080504.png

 

 

revenue_child_wo: = 
SUMX( 
    CALCULATETABLE('Table', REMOVEFILTERS()), 
        'Table'[QTY] * 'Table'[Revenue]
        )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
BeeleJa.pbix
Message 2 of 12
498 Views
0
BeeleJa
Helper I
Helper I
In response to Jihwan_Kim

‎05-05-2025 10:28 AM

thx for the quick response

how can I integratw this to the child wo value, my data set has multiple child wo under a single wo and multiple wo inclusing child wo

Message 3 of 12
487 Views
0
danextian
Super User
Super User
In response to BeeleJa

‎05-05-2025 03:44 PM

Hi @BeeleJa 

If the columns are from the same table, @Jihwan_Kim 's solution should work. Can you please be more specific? 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 4 of 12
454 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All