Page 1 of 2 12 LastLast
Results 1 to 10 of 12
Like Tree1Likes

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. #1
    New Member
    Join Date
    Mar 2014
    Posts
    29

    Unhappy 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.

    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\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. #2
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    533

    Default 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 @
    __________________________
    Imke

    Blog: www.TheBIccountant.com

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    29

    Default Re: Conditional Recursive Calculation [NEED HELP]

    Quote Originally Posted by ImkeF View Post
    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. #4
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    533

    Default Re: Conditional Recursive Calculation [NEED HELP]

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

    Blog: www.TheBIccountant.com

  5. #5
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263

    Default 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])))
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  6. #6
    New Member
    Join Date
    Mar 2014
    Posts
    29

    Default Re: Conditional Recursive Calculation [NEED HELP]

    Quote Originally Posted by ImkeF View Post
    I've collected some learning resources here: Learning resources The BIccountant
    Thanks, for the link.

    Quote Originally Posted by scottsen View Post
    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. #7
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    533

    Default 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"}}),
             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,
    __________________________
    Imke

    Blog: www.TheBIccountant.com

  8. #8
    New Member
    Join Date
    Mar 2014
    Posts
    29

    Default Re: Conditional Recursive Calculation [NEED HELP]

    Quote Originally Posted by ImkeF View Post
    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"}}),
             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,

  9. #9
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    197

    Default 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 (
            ADDCOLUMNS (
                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
    Last edited by Ozeroth; Jun 23rd, 2016 at 01:44 AM.
    ImkeF likes this.

  10. #10
    New Member
    Join Date
    Mar 2014
    Posts
    29

    Default Re: Conditional Recursive Calculation [NEED HELP]

    Quote Originally Posted by Ozeroth View Post
    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 (
            ADDCOLUMNS (
                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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com