Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Sum filter based on dynamic selected column value

Hi all,   My data looks like this: campaign date amount 1 3-6-2016 10 2 1-1-2017 10 3 1-4-2018 10 4 1-5-2018 10   What I hope to achieve is to sum the amount for all ...
  • prateekraina's avatar
    prateekraina
    8 years ago

    Hi Anonymous,

     

    I did it with another approach using Rolling total type approach.

     

    Created below calculated column:

    Rolling Sum = 
CALCULATE (
    SUM ( Table1[amount] ),
    FILTER (
        Table1,
        Table1[campaign] < EARLIER ( Table1[campaign] )
    )
)


    Created below measure:

    Sum of Campaign = 
VAR Calc =
    SUM ( Table1[Rolling Sum] )
RETURN
    IF (
        ISBLANK ( Calc ),
        0,
        Calc
    )

     

    And Done !!

     

    Refer to this pbix for details.

    Prateek Raina

prateekraina's avatar
prateekraina
Memorable Member
8 years ago

Hi Anonymous,

 

I did it with another approach using Rolling total type approach.

 

Created below calculated column:

Rolling Sum = 
CALCULATE (
    SUM ( Table1[amount] ),
    FILTER (
        Table1,
        Table1[campaign] < EARLIER ( Table1[campaign] )
    )
)


Created below measure:

Sum of Campaign = 
VAR Calc =
    SUM ( Table1[Rolling Sum] )
RETURN
    IF (
        ISBLANK ( Calc ),
        0,
        Calc
    )

 

And Done !!

 

Refer to this pbix for details.

Prateek Raina

Anonymous's avatar
Anonymous
Not applicable
8 years ago

thanks. works like a charm