Sum filter based on dynamic selected column value

[s-in-a-triangle]

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 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]

Hi @s-in-a-triangle,

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

[s-in-a-triangle]

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 · Accepted solution]

Hi @s-in-a-triangle,

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

[s-in-a-triangle]

thanks. works like a charm