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:

campaigndateamount
13-6-201610
21-1-201710
31-4-201810
41-5-201810

 

What I hope to achieve is to sum the amount for all the campaigns that are before the selected campaign.

For instance, if I select campaign 2 then the desired outcome would be 10, if I select campaign 4 the desired outcome would be 30.

 

I achieved to get the outcome, but I need to have it dynamically.

I now have:

Measure: rank = RANKX(all(Blad1[campaign]);CALCULATE(max(Blad1[date]);ALLEXCEPT(Blad1;Blad1[campaign])))

Column: rank_column = [rank]

Masure: rank_selected = CALCULATE([rank];Blad1[campaign]="3")

Column: rank_column_selected = [rank_selected]

Column: Amount_before = IF(Blad1[rank_column]>Blad1[rank_column_selected];Blad1[amount])

 

I would like to be able to use a slicer or an alternative way of filtering on this, so that I do not have to hard-code the campaignID (as I coded the "3" in the example). However, if I use the selectvalue option I get the following message: "A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

Any suggestion?

 

 

  • 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

4 Replies

  • 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