Conditional Recursive Calculation [NEED HELP]

pociners

New Member
Joined
Mar 19, 2014
Messages
32
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.

I've read on below link can do a recursive calculation:
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\CABCDFormula for D Column
000B - A = 000
1100200B - A = 100100=IF(D0+C1<0,0,D0+C1)
2700B - A = -7030=IF(D1+C2<0,0,D1+C2)
31000B - A = -1000=IF(D2+C3<0,0,D2+C3)
4100300B - A = 200200=IF(D3+C4<0,0,D3+C4)

<tbody>
</tbody>

My goal is D column, can you help me?

Thanks,

Budy.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Upvote 0
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/0...replacements-of-words-in-text-in-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?
 
Upvote 0
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() - sum([a]), filter(all(table1), table1[rc] <= earlier(table1[rc])))
 
Upvote 0
I've collected some learning resources here: Learning resources – The BIccountant

Thanks, for the link.

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() - 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?
 
Upvote 0
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,[B] RT=(if [RT]+Col{[Counter]} < 0 then 0 else [RT]+Col{[Counter]})],[/B]           
           each [RT]),
         LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total Condition"}}),
         TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
      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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "C", each [B]-[A]),
    Custom1 = fxRunningTotalCond(#"Added Custom", "C")
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,
 
Upvote 0
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,[B] RT=(if [RT]+Col{[Counter]} < 0 then 0 else [RT]+Col{[Counter]})],[/B]           
           each [RT]),
         LstHeadres = List.Combine({Table.ColumnNames(tab), {"Running Total Condition"}}),
         TblToCol = Table.FromColumns(List.Combine({Table.ToColumns(tab), {LstGen}}), LstHeadres)
      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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "C", each [B]-[A]),
    Custom1 = fxRunningTotalCond(#"Added Custom", "C")
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,
 
Upvote 0
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).
IndexAB
000
1100200
2700
31000
4100300

<tbody>
</tbody>

Then this calculated column should do the trick (should work in any version of DAX):
Code:
=
[COLOR=#ff0000]CALCULATE (
    SUM ( Table1[B] ) - SUM ( Table1[A] ),
    ALL ( Table1 ),
    Table1[Index] <= EARLIER ( Table1[Index] )[/COLOR]
[COLOR=#ff0000])[/COLOR]
    - MINX (
        ADDCOLUMNS (
            FILTER ( VALUES ( Table1[Index] ), Table1[Index] <= EARLIER ( Table1[Index] ) ),
            "Running Total",
[COLOR=#ff0000]            CALCULATE (
                SUM ( Table1[B] ) - SUM ( Table1[A] ),
                ALL ( Table1 ),
                Table1[Index] <= EARLIER ( Table1[Index] )
            )[/COLOR]
        ),
        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 :)
 
Last edited:
Upvote 0
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).
IndexAB
000
1100200
2700
31000
4100300

<tbody>
</tbody>

Then this calculated column should do the trick (should work in any version of DAX):
Code:
=
[COLOR=#ff0000]CALCULATE (
    SUM ( Table1[B] ) - SUM ( Table1[A] ),
    ALL ( Table1 ),
    Table1[Index] <= EARLIER ( Table1[Index] )[/COLOR]
[COLOR=#ff0000])[/COLOR]
    - MINX (
        ADDCOLUMNS (
            FILTER ( VALUES ( Table1[Index] ), Table1[Index] <= EARLIER ( Table1[Index] ) ),
            "Running Total",
[COLOR=#ff0000]            CALCULATE (
                SUM ( Table1[B] ) - SUM ( Table1[A] ),
                ALL ( Table1 ),
                Table1[Index] <= EARLIER ( Table1[Index] )
            )[/COLOR]
        ),
        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..
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top