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. CCCC9784097398, ArtNo.1000064370 i have Needed 1, 4, 1, and 1 = Total Qunatity 7

DESIRED OUTPUT

JobNoArtNo.NeededTypeTotal Quantity
CCCC9784097398Object1Object1
CCCC9784097398Object1Object1
CCCC9784097398Object1Object1
BBB38383838Object1Object1
BBB38383838Object20Object20
BBB38383838100028434148Article48
BBB38383838Object1Object1
BBB38383838Object1Object1
CCCC6564636510011828011Article1
CCCC6564636510011580711Article1
CCCC6564636510009577041Article1
CCCC65646365100025417012Article12
AAAA000910002581791Article1
CCCC978409739810028802080.08Article0.08
CCCC978409739810028452888Article8
BBB38383838100126583514Article39
BBB38383838100126583525Article39
BBB38383838100189831344Article44
BBB3838383810002832984Article4
CCCC978409739810012136524Article4
CCCC9784097398100121365120Article32
CCCC978409739810012136514Article32
CCCC978409739810012136514Article32
CCCC978409739810012136514Article32
CCCC978409739810002832982Article2
CCCC978409739810002823953Article3
CCCC978409739810002576963Article3
CCCC978409739810000643711Article1
CCCC978409739810000643701Article7
CCCC978409739810000643704Article7
CCCC978409739810000643701Article7
CCCC978409739810000643701Article7
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"
    )
)

5 Replies

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

  • danextian's avatar
    danextian
    Super User
    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
    Super User
    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
    Super User
    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"
    )
)