Calculated Column that sums values based on 2 other columns

[Anonymous]

Hi everyone, I need your help once again.

Im trying to create a calculated column that would do something like this:

Im trying to get the distinct sum of the values based on the distinct index and activity while disregarding the activities AA and CC. (Not sure if I made sense. haha) Basically, I want the values to only be summed once for each index and activity while still filling up all the rows.

Thank you,

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

Please create the calculated column with the formula below.

Column =
VAR t =
    SUMMARIZE (
        FILTER (
            Table1,
            NOT ( [Active] IN { "AA", "CC" } )
                && [Index] = EARLIER ( Table1[Index] )
        ),
        [Active],
        [Active Value]
    )
RETURN
    SUMX ( t, [Active Value] )

Here it the output.

Best Regards,

Cherry

[Anonymous]

Thank you so much @v-piga-msft ! it worked perfectly!

---

@v-piga-msft wrote:

Hi @Anonymous ,

Please create the calculated column with the formula below.

Column =
VAR t =
    SUMMARIZE (
        FILTER (
            Table1,
            NOT ( [Active] IN { "AA", "CC" } )
                && [Index] = EARLIER ( Table1[Index] )
        ),
        [Active],
        [Active Value]
    )
RETURN
    SUMX ( t, [Active Value] )

Here it the output.

Best Regards,

Cherry

---