Forum Discussion
Post ProdigySum 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
| JobNo | ArtNo. | Needed | Type | Total Quantity |
| CCCC9784097398 | Object | 1 | Object | 1 |
| CCCC9784097398 | Object | 1 | Object | 1 |
| CCCC9784097398 | Object | 1 | Object | 1 |
| BBB38383838 | Object | 1 | Object | 1 |
| BBB38383838 | Object | 20 | Object | 20 |
| BBB38383838 | 1000284341 | 48 | Article | 48 |
| BBB38383838 | Object | 1 | Object | 1 |
| BBB38383838 | Object | 1 | Object | 1 |
| CCCC65646365 | 1001182801 | 1 | Article | 1 |
| CCCC65646365 | 1001158071 | 1 | Article | 1 |
| CCCC65646365 | 1000957704 | 1 | Article | 1 |
| CCCC65646365 | 1000254170 | 12 | Article | 12 |
| AAAA0009 | 1000258179 | 1 | Article | 1 |
| CCCC9784097398 | 1002880208 | 0.08 | Article | 0.08 |
| CCCC9784097398 | 1002845288 | 8 | Article | 8 |
| BBB38383838 | 1001265835 | 14 | Article | 39 |
| BBB38383838 | 1001265835 | 25 | Article | 39 |
| BBB38383838 | 1001898313 | 44 | Article | 44 |
| BBB38383838 | 1000283298 | 4 | Article | 4 |
| CCCC9784097398 | 1001213652 | 4 | Article | 4 |
| CCCC9784097398 | 1001213651 | 20 | Article | 32 |
| CCCC9784097398 | 1001213651 | 4 | Article | 32 |
| CCCC9784097398 | 1001213651 | 4 | Article | 32 |
| CCCC9784097398 | 1001213651 | 4 | Article | 32 |
| CCCC9784097398 | 1000283298 | 2 | Article | 2 |
| CCCC9784097398 | 1000282395 | 3 | Article | 3 |
| CCCC9784097398 | 1000257696 | 3 | Article | 3 |
| CCCC9784097398 | 1000064371 | 1 | Article | 1 |
| CCCC9784097398 | 1000064370 | 1 | Article | 7 |
| CCCC9784097398 | 1000064370 | 4 | Article | 7 |
| CCCC9784097398 | 1000064370 | 1 | Article | 7 |
| CCCC9784097398 | 1000064370 | 1 | Article | 7 |
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")))
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] ) )Hi AllanBerces,
Try below Dax to create a calculated column:-
Total Quantity =VAR _Type = 'Table'[Type]VAR _Job = 'Table'[JobNo]VAR _Art = 'Table'[ArtNo]
RETURNIF (_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!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
Super User
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
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] ) )
AllanBercesHi cengizhanarslan grazitti_sapna danextian krishnakanth240 thank you very much all good.
- grazitti_sapna
Hi AllanBerces,
Try below Dax to create a calculated column:-
Total Quantity =VAR _Type = 'Table'[Type]VAR _Job = 'Table'[JobNo]VAR _Art = 'Table'[ArtNo]
RETURNIF (_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
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" ) )