SUMIF equivalent to creat new column within one table

[PatrickdeJongh]

I'm trying to calculate a new column, Weighting 2 that sums the weighting for each CourseModule where AssignmentDueDate is less than or equal to the current value set by a slicer. 
I made a measure called Success Rate Date to hold the slicer value of the slicer. 
All the data is in one table in a Database called Coursedocument.

I need the Weighting 2 to be dynamic as the user can set the slicer to adjust AssignmentDueDate and thus the Success Rate Date.  I will use the Weighting 2 values to do other calculations. Weighting 2 will be 100 or less for each row.

I have tried a few variances of the Dax functions:

Weighting 2 =

SUMMARIZE(Coursedocument, Coursedocument[CourseModule],"Weighting 2", CALCULATE(SUM(Coursedocument[Weighting]), FILTER(

            Coursedocument,

            Coursedocument[AssignmentDueDate] <= Coursedocument[Success Rate Date]

        )))

Weighting 2 =

SUMMARIZE(Coursedocument, Coursedocument[CourseModule], "Weighting 2", CALCULATE(SUM(Coursedocument[weighting]),

          Coursedocument[AssignmentDueDate] <= Coursedocument[Success Rate Date]

        ))

Example data (from Database)

A	B	C	D	E
Id	CourseModule	Weighting	AssignmentDueDate	Weighting 2
15914	3495	100	28/02/2023	100
18461	3986	100	31/05/2023	100
18538	4002	7	31/03/2023	22
18539	4002	15	31/03/2023	22
18544	4004	15	31/01/2023	100
18545	4004	20	31/01/2023	100
18546	4004	10	28/02/2023	100
18547	4004	15	28/02/2023	100
18548	4004	20	28/02/2023	100
18549	4004	20	28/02/2023	100
19011	4186	15	31/01/2023	25
19012	4186	10	31/01/2023	25
19019	4187	12	28/02/2023	56
19020	4187	7	28/02/2023	56
19021	4187	15	31/03/2023	56
19022	4187	7	31/07/2023	56
19023	4187	15	31/07/2023	56

In Excel I would simply use Weighting 2  =SUMIF(B:B,B4,C:C) and drag the formula down. 
How can I get this right as a Column added to my table? 

[v-sshirivolu · Accepted solution]

Hi @PatrickdeJongh ,

A calculated column will not meet your needs, as it remains static and does not respond to slicers. To achieve dynamic behavior, it is recommended to use a measure instead.

You can implement the following:

Cumulative Weighting =
VAR CurrentDate = MAX('Table'[AssignmentDueDate])
VAR CurrentModule = MAX('Table'[CourseModule])
RETURN
    CALCULATE(
        SUM('Table'[Weighting]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[AssignmentDueDate] <= CurrentDate
                && 'Table'[CourseModule] = CurrentModule
        )
    )

ALLSELECTED allows the measure to update dynamically with slicers, such as Success Rate Date.

It functions similarly to a SUMIF for each module up to the selected date.

When added to a Table or Matrix visual with CourseModule and AssignmentDueDate, it will display as a column and remain interactive.

This solution provides the dynamic functionality you require, which cannot be accomplished with a calculated column.

[PatrickdeJongh]

This is the closest solution so far. when testing it against data from the table it works some time and not other time. I am exploring why. 
Thank you as well for explaining why you use the ALLSELECT function. The explanasion really helps me learn.

[grazitti_sapna]

Hi @PatrickdeJongh ,

You can try using:- 
Weighting 2 =
VAR _CourseModule = MAX ( Coursedocument[CourseModule] )
VAR _Date =
MAX ( Coursedocument[Success Rate Date] )
RETURN
CALCULATE (
SUM ( Coursedocument[Weighting] ),
FILTER (
ALL ( Coursedocument ),
Coursedocument[AssignmentDueDate] <= _Date
&& Coursedocument[CourseModule] = _CourseModule
)
)

🌟 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!

[PatrickdeJongh]

if I remove the need for the Weighting 2 values to be variable, how would I get the calculation to work based on the value in the table? This needs to be a calculation as I need a value for each row in the table.

[GeraldGEmerick]

@PatrickdeJongh You cannot create a column since calculated columns are not dynamic. However, you can create a measure like the following:

Measure = 
    VAR _CourseModule = MAX( 'Table'[CourseModule] )
    VAR _Date = MAXX( ALLSELECTED( 'Table'[AssignmentDueDate] ), [AssignmentDueDate] )
    VAR _Return = CALCULATE( SUM( 'Table'[Weighting] ), FILTER( ALL( 'Table' ), [AssignmentDueDate] <= _Date && [CourseModule] = _CourseModule ) )
RETURN
    _Return

[PatrickdeJongh]

Thank you for the help. I will have to think of another way to get the values to update upon Success Rate Date selection.