# Conditional Recursive Calculation [NEED HELP]

This is a discussion on Conditional Recursive Calculation [NEED HELP] within the Power BI forums, part of the Question Forums category; Hi Everyone, This is Budy from Indonesia. I want to do a recursive calculation in Power Pivot. The calculation is ...

1. ## Conditional Recursive Calculation [NEED HELP]

Hi Everyone,

This is Budy from Indonesia.
I want to do a recursive calculation in Power Pivot. The calculation is very simple actually, if I do in excel sheet, it'd take less than a minute to finish that, but I wanna do that in Power Pivot.

Recursive Calculations in PowerPivot using DAX | Gerhard Brueckl's BI Blog
But on that link only convert a recursive calculation into a mathematical logic. But however the idea is very brilliant,haha..

Well, this is my table data:

 R\C A B C D Formula for D Column 0 0 0 B - A = 0 0 0 1 100 200 B - A = 100 100 =IF(D0+C1<0,0,D0+C1) 2 70 0 B - A = -70 30 =IF(D1+C2<0,0,D1+C2) 3 100 0 B - A = -100 0 =IF(D2+C3<0,0,D2+C3) 4 100 300 B - A = 200 200 =IF(D3+C4<0,0,D3+C4)

My goal is D column, can you help me?

Thanks,

Budy.

2. ## Re: Conditional Recursive Calculation [NEED HELP]

Don't think that there is a solution in Power Pivot (DAX) for it. But if you consider Power Query as an extension, it is still possible :

List.Generate is a good function for that task:
https://blog.crossjoin.co.uk/2014/06...n-power-query/
or List.Generate(): Create Tables from Thin Air in Power BI with M! - PowerPivotPro

or you use the generic recursive function like described here: https://msdn.microsoft.com/en-us/library/mt185361.aspx - using an @

3. ## Re: Conditional Recursive Calculation [NEED HELP]

Originally Posted by ImkeF
Don't think that there is a solution in Power Pivot (DAX) for it. But if you consider Power Query as an extension, it is still possible :

List.Generate is a good function for that task:
https://blog.crossjoin.co.uk/2014/06...n-power-query/
or List.Generate(): Create Tables from Thin Air in Power BI with M! - PowerPivotPro

or you use the generic recursive function like described here: https://msdn.microsoft.com/en-us/library/mt185361.aspx - using an @
Well.. I'm very new in power query, I need some time to learn this,
Do you have a reference to learn power query?

4. ## Re: Conditional Recursive Calculation [NEED HELP]

I've collected some learning resources here: Learning resources – The BIccountant

5. ## Re: Conditional Recursive Calculation [NEED HELP]

Just in case you misspoke ...

Any chance you actually want a simple running total (plus a floor at 0) ? It's not what you said, but at least that is way easier

=calculate(sum([b]) - sum([a]), filter(all(table1), table1[rc] <= earlier(table1[rc])))

6. ## Re: Conditional Recursive Calculation [NEED HELP]

Originally Posted by ImkeF
I've collected some learning resources here: Learning resources – The BIccountant

Originally Posted by scottsen
Just in case you misspoke ...

Any chance you actually want a simple running total (plus a floor at 0) ? It's not what you said, but at least that is way easier

=calculate(sum([b]) - sum([a]), filter(all(table1), table1[rc] <= earlier(table1[rc])))
Yap, Actually just simple running total and floor at 0. But I dont know how to manage it in power pivot.
I've done the formula similar like yours, but I have a wrong result. In D4 Column I got 130, not 200.
Can you help me?

7. ## Re: Conditional Recursive Calculation [NEED HELP]

List.Generate isn't the easiest function, so this might be a bit demanding for a beginner. So please find the code as follows. It comes with sample data, so you just copy all the code below and paste it into the advanced editor (replacing everything that's already in there (let Source = "" in Source) with it):

Code:
```let
fxRunningTotalCond = (tab as table, col_name as text) =>

let
Col = List.Buffer(Table.Column(tab, col_name)),
ListCount = List.Count(Col),
LstGen = List.Generate(()=>
[Counter=1, RT= Col{0}],
each [Counter]<=ListCount,
each [Counter=[Counter]+1, RT=(if [RT]+Col{[Counter]} < 0 then 0 else [RT]+Col{[Counter]})],
each [RT]),
LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total Condition"}}),
in
TblToCol,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSgeJYnWglQyDL0ADENzKAiBgB2eYIBcZwBRC+CZxvDNIQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"R/C" = _t, A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"R/C", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}}),
in
Custom1```
Then the full table will be shown. In order to connect it with your data table (assuming that it is called "table1") you then need to replace the Source-step with this:

Code:
`Source = table1,`

8. ## Re: Conditional Recursive Calculation [NEED HELP]

Originally Posted by ImkeF
List.Generate isn't the easiest function, so this might be a bit demanding for a beginner. So please find the code as follows. It comes with sample data, so you just copy all the code below and paste it into the advanced editor (replacing everything that's already in there (let Source = "" in Source) with it):

Code:
```let
fxRunningTotalCond = (tab as table, col_name as text) =>

let
Col = List.Buffer(Table.Column(tab, col_name)),
ListCount = List.Count(Col),
LstGen = List.Generate(()=>
[Counter=1, RT= Col{0}],
each [Counter]<=ListCount,
each [Counter=[Counter]+1, RT=(if [RT]+Col{[Counter]} < 0 then 0 else [RT]+Col{[Counter]})],
each [RT]),
LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total Condition"}}),
in
TblToCol,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSgeJYnWglQyDL0ADENzKAiBgB2eYIBcZwBRC+CZxvDNIQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"R/C" = _t, A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"R/C", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}}),
in
Custom1```
Then the full table will be shown. In order to connect it with your data table (assuming that it is called "table1") you then need to replace the Source-step with this:

Code:
`Source = table1,`
Wooww.. you are so kind.. thank you so much..
I will learn power query ASAP so I can use your code..

Anyway, if anyone has a solution for my case via powerpivot, I'd be appreciated..
Thanks,

9. ## Re: Conditional Recursive Calculation [NEED HELP]

Hi Budy,

If you want to do this with DAX as a calculated column (a measure will be similar but different):

I'm going to assume you have a table called Table1 with columns named Index, A, B (change as needed).
 Index A B 0 0 0 1 100 200 2 70 0 3 100 0 4 100 300

Then this calculated column should do the trick (should work in any version of DAX):
Code:
```=
CALCULATE (
SUM ( Table1[B] ) - SUM ( Table1[A] ),
ALL ( Table1 ),
Table1[Index] <= EARLIER ( Table1[Index] )
)
- MINX (
FILTER ( VALUES ( Table1[Index] ), Table1[Index] <= EARLIER ( Table1[Index] ) ),
"Running Total",
CALCULATE (
SUM ( Table1[B] ) - SUM ( Table1[A] ),
ALL ( Table1 ),
Table1[Index] <= EARLIER ( Table1[Index] )
)
),
IF ( [Running Total] < 0, [Running Total] )
)```
The red code,which appears twice, is the Running Total of B - A.
The formula computes:
1. The Running Total of B - A
2. Minus the most negative Running Total of B - A that has appeared so far

which replicates the behaviour of your recursive formula.

Does that work for you?

Owen

10. ## Re: Conditional Recursive Calculation [NEED HELP]

Originally Posted by Ozeroth
Hi Budy,

If you want to do this with DAX as a calculated column (a measure will be similar but different):

I'm going to assume you have a table called Table1 with columns named Index, A, B (change as needed).
 Index A B 0 0 0 1 100 200 2 70 0 3 100 0 4 100 300

Then this calculated column should do the trick (should work in any version of DAX):
Code:
```=
CALCULATE (
SUM ( Table1[B] ) - SUM ( Table1[A] ),
ALL ( Table1 ),
Table1[Index] <= EARLIER ( Table1[Index] )
)
- MINX (
FILTER ( VALUES ( Table1[Index] ), Table1[Index] <= EARLIER ( Table1[Index] ) ),
"Running Total",
CALCULATE (
SUM ( Table1[B] ) - SUM ( Table1[A] ),
ALL ( Table1 ),
Table1[Index] <= EARLIER ( Table1[Index] )
)
),
IF ( [Running Total] < 0, [Running Total] )
)```
The red code,which appears twice, is the Running Total of B - A.
The formula computes:
1. The Running Total of B - A
2. Minus the most negative Running Total of B - A that has appeared so far

which replicates the behaviour of your recursive formula.

Does that work for you?

Owen
Hello Mr. Owen..

Actually your code doesn't work for me, it shows error in my powerpivot, but it's ok, I will modify it by myself. But However your mathematical logic is very aaawwwwweeeesssoooommmeee!!!!!!
I was a mathematics student actually. My major in college is math, but i stuck with this simple case, haha..

Thank you soo much sir for your help!!!!
Best regards..

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•