 # Recursive Calculation

Good Day,

I require some assistance with developing a DAX measure for the following scenario:

I want to do something similar to Cummulative totals but the totals are reducing. So as you can see in the image below:

• I have a certain cost of Production/sales (Say \$100)
• Certain projects get implemented and these projects realize a certain % saving (Projects implemented yearly - only looking at yearly savings - at multiple sites and currently there are over a hundred projects)
• This means that the cost of production after Project 1 (P1) (10% saving) was implemented is now \$ 90
• Project 2 (P2) now realize a saving of 5%. This saving however is now on 90 (100 - 10%) and not on the 100
• So the cost of production is now \$ 85.50 after P2 gets implemented (90 * 95% )

How do I calculate the Cost after saving per project dynamically? Do I calculate this similar to
Cumulative totals but subtracting this saving every time?

Hi @nico.swartz,

I think Power Query is better suited for this job, using List.Generate

First add an Index from 0

And then a Custom Column, with this logic.

``````myFunction = Table.AddColumn(AddIndex, "Calc Costs after Saving", each
let
myList = List.Generate(
()=> [pCost = pCost, i = 0, y = List.Max(iList) ],
each [i] <= [y],
each [pCost = [pCost] * pList{[i]+1}, i = [i]+1, y = [y] ]
),
CreateList = Table.FromRecords(myList){[Index]}[pCost]
in
CreateList, type number )
``````

Here’s my sample file. eDNA - Recursive Calculation.pbix (21.8 KB)

3 Likes

Thanks, Melissa for your feedback. Looks like it can be a solution.

So you think it would be a better solution if I should Generate this list in the query editor rather than using a dax measure?

As far as I know recursive formulas may have a pattern that can be derived but you cannot recursively self-reference a column in DAX.

After some further exploration I came up with this pattern.

``````Costs after Saving (Measure) =
VAR vTable =
"@Value", PRODUCTX(
FILTER( ALL('Sample'),
'Sample'[Index] <= EARLIER( 'Sample'[Index] )
),  'Sample'[(1-%Saved)]
)* 'Sample'[Production Cost]
)
RETURN

SUMX( vTable, [@Value] )
``````