Forum Discussion
Filter for cumulative total
Hi All,
I would like to conditionally summarize categories where we had data until now in the year.
For example:
1th month we had value1 only for cat2, so the result would be 20
2nd month we had value1 for cat1 and had value1 for cat2 earlier so the result would be 30
When I try to add SUM in the filter clause of CALCULATE then I got error.
Could you give some hint, please?
3 Replies
Value1 and Value2 are both columns in the table.
Value1 should be in the filter criteria and I have to check whether it has value until this year or not. Maybe I should count with it as running total in an extra calculated column.
Value2 is the value what I would like to summarize, depending on which month is selected and value1 values until now for that category.
If Value1 "activates" a category then the Value2 for that category must be included in the total, otherwise not.
- tamerj1
Community Champion
Zoltan10
Not sure if I fully understand but you may try following two options (calculated column and measure)Total Value = VAR CurrentMonth = Sheet1[Month] RETURN CALCULATE ( SUMX ( Sheet1, Sheet1[Value1] * Sheet1[Value2] ), Sheet1[Month] <= CurrentMonth, REMOVEFILTERS () )Total Value Measure = VAR CurrentMonth = MAX ( Sheet1[Month] ) RETURN CALCULATE ( SUMX ( Sheet1, Sheet1[Value1] * Sheet1[Value2] ), Sheet1[Month] <= CurrentMonth, REMOVEFILTERS () )
