Forum Discussion
SUMIF equivalent to creat new column within one table
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?
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.
6 Replies
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.
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.
- GeraldGEmerick
Memorable Member
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 _ReturnThank you for the help. I will have to think of another way to get the values to update upon Success Rate Date selection.
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.
- grazitti_sapna
Super User
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!
