Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
3 months ago
Solved

Sum with Condition

Hi can someone correct my calculated column. On my table if the Type is Object the equal to Needed but if the type is Article i want the sum of Needed base from ArtNo. and JobNo.  Sample JobNo. CCCC...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    3 months ago

    Hi AllanBerces 

    Can you try this calculation

    Total Quantity =

    IF('Table'[Type] = "Object", 'Table'[Needed],

    CALCULATE(SUM('Table'[Needed]),

    FILTER('Table',

    'Table'[JobNo] = EARLIER('Table'[JobNo]) &&

    'Table'[ArtNo.] = EARLIER('Table'[ArtNo.]) &&

    'Table'[Type] = "Article")))

  • danextian's avatar
    danextian
    3 months ago

    hi AllanBerces 

    Try this:

    Total Qty = 
VAR _job = 'Table'[JobNo]
VAR _art = 'Table'[ArtNo.]
RETURN
    SWITCH (
        'Table'[Type],
        "Object", 'Table'[Needed],
        "Article",
            SUMX (
                FILTER ( 'Table', 'Table'[ArtNo.] = _art && 'Table'[JobNo] = _job ),
                [Needed]
            )
    )

     

  • grazitti_sapna's avatar
    grazitti_sapna
    3 months ago

    Hi AllanBerces,

     

    Try below Dax to create a calculated column:-

     

    Total Quantity =
    VAR _Type = 'Table'[Type]
    VAR _Job = 'Table'[JobNo]
    VAR _Art = 'Table'[ArtNo]

    RETURN
    IF (
        _Type = "Object",
        'Table'[Needed],
        CALCULATE (
            SUM ( 'Table'[Needed] ),
            FILTER (
                'Table',
                'Table'[JobNo] = _Job
                    && 'Table'[ArtNo] = _Art
                    && 'Table'[Type] = "Article"
            )
        )
    )
     
    Also refer attached .pbix file with solution.
     
    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!
  • cengizhanarslan's avatar
    cengizhanarslan
    3 months ago

    Please try the calculated column below:

    Total Quantity =
IF (
    'Table'[Type] = "Object",
    'Table'[Needed],
    CALCULATE (
        SUM ( 'Table'[Needed] ),
        ALLEXCEPT ( 'Table', 'Table'[ArtNo.], 'Table'[JobNo.] ),
        'Table'[Type] = "Article"
    )
)
krishnakanth240's avatar
krishnakanth240
Super User
3 months ago

Hi AllanBerces 

Can you try this calculation

Total Quantity =

IF('Table'[Type] = "Object", 'Table'[Needed],

CALCULATE(SUM('Table'[Needed]),

FILTER('Table',

'Table'[JobNo] = EARLIER('Table'[JobNo]) &&

'Table'[ArtNo.] = EARLIER('Table'[ArtNo.]) &&

'Table'[Type] = "Article")))