Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
WLFRD
Helper III
Helper III

Sum quantity per partcode

Hello,

 

I'm struggling with the sum of quantities per partnumber. Our machines have framenumbers and those machines are build with parts. I would like to sum the parts in an overview.

This is the data:

FramenumberPartNumberQuantity
01504M1124552
01504M23857422
01504M5987495
01504M2548754
01504M25415211
01504M65874523
01504M1589651
01504M12854445
01504M1236597
01504M1285889
01504M1245882
01605D1124552
01605D23857422
01605D5987495
01605D2548754
01605D25415211
01605D65874523
01605D1589651
01605D12854445
01605D1236597
01605D1285889
01605D1245882
07822E1124552
07822E23857422
07822E5987495
07822E2548754
07822E25415211
07822E65874523
07822E1589651
07822E12854445
07822E1236597
07822E1285889
07822E1245882

 

And the result should look like this:

PartNumberQuantity
1124556
23857466
59874915
25487512
25415233
65874569
1589653
128544135
12365921
12858827
1245886
1124556
23857466
59874915
25487512
25415233
65874569
1589653
128544135
12365921
12858827
1245886

 

I tried this but it seems that the total QTY is summed and multiplied by three:

Total_QTY_per_PartNumber = CALCULATE(SUM(ProductionPartAll[Quantity]), ALLEXCEPT(ProductionPartAll, ProductionPartAll[PartNumber]))
 
Can someone help me out?
 
Thanks in advance.
 
1 ACCEPTED SOLUTION
Samarth_18
Community Champion
Community Champion

oh okay @WLFRD , then try this:-

Column =
CALCULATE (
    SUM ( 'ProductionPartAll'[Quantity] ),
    FILTER (
        'ProductionPartAll',
        'ProductionPartAll'[PartNumber] = EARLIER ( 'ProductionPartAll'[PartNumber] )
    )
)

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

7 REPLIES 7
Samarth_18
Community Champion
Community Champion

Hi @WLFRD ,

 

You can directly drag your partnumber and Quantity column then mark Quantity column summerization as Sum.

 

Samarth_18_0-1644915071720.png

 

Thanks,

Samarth

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Thanks! But I would like to have a calculated column. I need this total column for another calculation.

Samarth_18
Community Champion
Community Champion

oh okay @WLFRD , then try this:-

Column =
CALCULATE (
    SUM ( 'ProductionPartAll'[Quantity] ),
    FILTER (
        'ProductionPartAll',
        'ProductionPartAll'[PartNumber] = EARLIER ( 'ProductionPartAll'[PartNumber] )
    )
)

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Is it possible to make the calculation dynamic? I mean, based on how we filter the data, it has to calculate the sum per PartNumber. 

vanessafvg
Super User
Super User

what result are you getting?  can you share?

 

I get this result from your measure

 

vanessafvg_0-1644914854523.png

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Is it possible to make the calculation dynamic? I mean, based on how we filter the data, it has to calculate the sum per PartNumber. 

This is my result in Power BI:

 

PartNumberTotal_QTY_Per_PartNumber
128544405
658745207
238574198
25415299
12858881
12365963
59874945
25487536
11245518
12458818
1589659

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.