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,

 

Change rank_started measure to below:

rank_selected =
CALCULATE (
    [rank],
    FILTER (
        Blad1,
        Blad1[campaign] = SELECTEDVALUE(<Your Column>)
    )
)

Replace <Your Column> with your actual column which contains the value.

 

Prateek Raina

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi prateekraina,

     

    Thank you for your prompt reply.

     

    Your formula works for the measure, but gives an error later on in my columns:

    "A circular dependency was detected: Blad1[rank_column_selected], Blad1[Amount_before], Blad1[rank_column_selected]."

     

    If I only use the measure I can circumvent the error message, but, unforunately, do not manage to get the desired outcome.

    I could write a measure like: if([rank_selected]>[rank];sum(Blad1[amount]))

    But this of course isn't evaluated on a row by row basis and hence does not give me the desired outcome.

     

    Any suggestions? Or perhaps an alternative approach to reach my desired end outcome?

     

    Thank you again.

     

    Best,

    Sander in a triangle

    • 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