Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculated Column that sums values based on 2 other columns

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,

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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