Search code examples
powerbirowformulapowerquery

Extend rows of hardcoded table with formula Power BI

I manually (hardcoded) made a table with 2 rows, which are my starting values, in Power BI: enter image description here

Now I want to extend the amount of rows of this table but I want the next value to be: 1.3% - (1.3% * 5%) = 1.24% Further, I want the row after this row to contain the value: 1.24% - (1.24% * 5%) = 1.17% and so on.

Does someone know how to do this in Power BI?

Thanks in advance!

Solution

  • Basically you are doing 1.3*(1-.05)^x where x=row number, so...

    let  Source = {0..100}, //edit 100 higher for more rows
Process = List.Transform(Source, each 1.3*Number.Power((1-.05),_)), //1.3*(1-.05)^x
Output = Table.FromColumns({Process},{"Name"})
in Output

    enter image description here

    EDIT:

    sample of two columns

    let  Source = {0..100}, //edit 100 higher for more rows
Process = List.Transform(Source, each 1.3*Number.Power((1-.05),_)), //1.3*(1-.05)^x
Process2 = List.Transform(Source, each -2.5*Number.Power((1-.3),_)), // -2.5*(1-.3)^x
Output = Table.FromColumns({Process}&{Process2},{"First","Second"})
in Output

    enter image description here

    EDIT2: cumulative sums

    let  Source = {0..100}, //edit 100 higher for more rows
Process = List.Transform(Source, each List.Sum(let a=_ in List.Transform({0..a}, each 1.3*Number.Power((1-.05),_)))),
Process2 = List.Transform(Source, each List.Sum(let a=_ in List.Transform({0..a}, each -2.5*Number.Power((1-.3),_)))),
Output = Table.FromColumns({Process}&{Process2},{"First","Second"})
in Output

    or

    let  Source = {0..100}, //edit 100 higher for more rows
Process = List.Transform(Source, each 1.3*Number.Power((1-.05),_)), //1.3*(1-.05)^x
Process2 = List.Transform(Source, each -2.5*Number.Power((1-.3),_)), // -2.5*(1-.3)^x
Output = Table.FromColumns({Process}&{Process2},{"First","Second"}),
#"Added Index" = Table.AddIndexColumn(Output, "Index", 0, 1, Int64.Type),
#"Added Cum Total" = Table.AddColumn(#"Added Index", "FirstCum", each List.Sum(List.FirstN(#"Added Index"[First],[Index]+1))),
#"Added Cum Total2" = Table.AddColumn(#"Added Cum Total", "SecondCum", each List.Sum(List.FirstN(#"Added Index"[Second],[Index]+1)))
in #"Added Cum Total2"