CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9714

How does CALCULATE, SUM, MAX and <= logically work for cumulative total?

Cumulative Total =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER (
        ALL ( Table[Index] ),
        Table[Index]
            <= MAX ( Table[Index] )
    )
)

I'm trying to understand how the above formula logically works.

1st the CALCULATEs 2nd parameter gets evaluated. The 2nd param returns a filtered table containing a single column.

In the FILTER the 1st param removes any filter from the index column. The 2nd param of filter checks that the index should be less than or equal to max from outer table.

But how does the CALCULATEs SUM do cumulative? Does FILTER work for each record of the SUM?

Upvotes: 0

Views: 82

Answers (1)

sergiom
sergiom

Reputation: 4887

FILTER is an iterator, CALCULATE is not. In this formula FILTER returns a table with a single column, 'Table'[Index], containing all of the Indexes that are less or equal to the MAX( 'Table'[Index] ) evaluated in the filter context existing when CALCULATE is executed.

When SUM( 'Table'[Value] ) is evaluated inside CALCULATE, the filtered table evaluated in the previous step is applied as filter context. This replaces any filter context existing on the same column, but is combined with possible other filter existing on other columns.

These filters are applied on Table and only the rows satisfying these criteria will be iterated by the SUM.

So it is not CALCULATE that iterates over Table. And SUM iterates on Table already filtered by the filter contex modified by CALCULATE.

Upvotes: 2

Related Questions