Running total / cumulative sum with a target value in dax

[jfletch]

I have a dataset of companies and the revenue they have achieved in a particular year. I am new to DAX and I need to get a running total measure of those revenues such that the running total does not go beyond a certain target or limit.

Year	Ranking	Company	Revenue	RT( Target = 14)
2022	1	ABC	10	10
2022	2	DEF	9	10
2022	3	GHI	3	13
2023	4	JKL	1	14
2023	5	MNO	1	14

Assuming the target below is 14, any time the running total is greater than 14, we maintain the last running total until the total is less than 14.

In the example above: ABC (10) + DEF (9)  = 19, so running total stays at previous 10 on line 2. DEF (now 10) + GHI (3) = 13, so running total is now 13 on line 3. GHI (13) + JKL (1) = 14, so running total is now 14 on line 4. JKL (14) + MNO (1) is 15, so running total stays at 14 on line 5.

This can be achieved with the Excel formula IF(D2+B3 >14,D2, D2+B3). I am able to get the running total in DAX but I'm unable to customise this with a target in mind. 

RunningTotal = CALCULATE(sum('Dataset'[Revenue]),

    filter(ALLSELECTED('Dataset'[Year], 'Dataset'[Company], 'Dataset'[Ranking]),

    'Dataset'[Ranking] <= max('Dataset'[Ranking])

    ))

Please help.

[v-yueyunzh-msft]

Hi , @jfletch 

According to your description, you want to get the target value by the rankx and when the sum of revenue greater than 14 it will keep above value.

For you need , the best way is to realize in Power Query Editor.

We can click "New Custom Column" and enter ：

List.Accumulate(Table.SelectRows(#"Changed Type",(a)=>a[Ranking]<=[Ranking])[Revenue] ,0,(x,y)=>if x+y>14 then x else x+y)

Then we can get this :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jfletch]

@v-yueyunzh-msft , thanks for this. I am looking for a measure so that when people apply filters on year or company, the measure will recalibrate again on the fly

[v-yueyunzh-msft]

Hi , @jfletch 

For your need , it is a recursion  problem. And in my work experience , i encounter this problem several times and still have no way to get .

So If you have a problem that calls for recursion in DAX... well, then you've basically got 2 choices:

1) either you'll find a non-recursive formula or

2) you'll move the calculation to Power Query/source system. The best solution is to be able to find a non-recursive formula, of course, and sometimes it's possible with a bit of mathematical wizardry but sometimes, sadly, it's simply impossible or just too difficult to do. Then, of course, you don't have a choice :d isappointed_face： By experience I can tell you that if you have a formula with IF's in it and it's recursive, then there are very slim chances to obtain an iterative one...

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jfletch]

Thanks @v-yueyunzh-msft, I'm hoping by my question in this forum I will find help to land a non-recursive formula (in a measure) that addresses my need.

[lbendlin]

Note that the MIN() function can take two arguments.

RunningTotalCapped = MIN(RunningTotal,14)

[jfletch]

@lbendlin can you please explain further? How do you see this solving my problem?

[lbendlin]

Calculate the running total as usual. Then apply the MIN() function to the result.