Calculation measures and append to table - Iterative macro?

Question

Dear Community,

I would appreciate if someone could support me with the problem I am struggling with.

I have a table with measures and values of those measures:

Based on that, I need to calculated new measures and I have a mapping table:

New measure column groups the measures from the first table and factor indicates the sign for group by - for example Cal Measure 1 calculation is Measure2 minus Measure1. But then Measure2 is the sum of Measure 3 and previously calculated CalcMeasure1. If I make a simple join, CalcMeasure2 has only 1 component (measure3), as CalcMeasure1 (from Measure column) falls into right ancor of the join. I have some work around - taking measures which all components can be found in the first table, then append them to the first table. After that another join with measures which components are previously calculated and another append. However it is not really sufficient. Is it possible to make some iterative macro for example, which could the CalcMeasure1 (Group 1) calculate, append to the first table, than take the CalcMeasure2, calculate and append to the first table. The loop would end when all CalcMeasures are calculated, it would be around 150 New CalcMeasures.

Thank you in advance,

Javier0705

DavidSta · Accepted Answer

Hi @Javier070590,

please find attached a sample workflow.

This was not a easy challenge, and I don't know if the solution I created is the easiest one, but it's working.

At first I create the Formulas you want to use

And as the Dynamic Replace is not able to use previous fields in the same calculation we really have to use an iterative Macro.

So I add the Formulas to the original Dataset, at the end it looks like this

Within the Macro I make sure to start with the first KPI. Once the first in done the 2nd takes over in the next iteration and so on.

At the end I check if there is a KPI left I need to calculate. If not stop and output all results. I can identify the new calculations, as I gave them all a unique identifier starting with "__CALC__" followed by a 5 digit number. So I can ensure the sorting and know if something is left.

Hope this one helps you to solve your requirements.

Best regards,

David