Iterator function with too many calculations

[sheepworrier]

Hi

I am trying to migrate an Excel scenario modelling tool into Power BI.  The Excel spreadsheet has a small table of data: ~40 rows and ~100 columns and there are ~40 scenario modelling inputs.  These inputs are generally % fields along the lines of "increase one of the ~100 columns by X%".  So this seems like a good fit for Power BI, DAX and some What-If Parameters.  However, I need to calculate the result of 40+ calculations at row-level and the average of the results for the ~40 rows.  I'm attempting to do this using AVERAGEX, but the DAX expression is getting longer and longer.  Ideally I'd like to split this up into intermediate measures or better yet variables within a single measure.  Here's a simplified view of the data and model in Excel:

Even with this basic example the DAX starts to get complex and contains repetition of the formula in column D

Calc4 =
AVERAGEX(
    'Table1'
    'Table1'[Raw Data 1] * (1 + [parameter1]) +
    ('Table1'[Raw Data 2] * (1 + [parameter2]) /
     'Table1'[Raw Data 1] * (1 + [parameter1]))

 Ideally I'd like to create intermediate steps like there is in Excel, but can't figure this out with any combination of VAR, CALCULATE or AVERAGEX that I tried.

In the real example my DAX expression is already hundreds of lines long and I'm only about halfway through.  Every step that refers back to earlier steps is then increasing the expression length exponentially.

Can anyone suggest any way to do this?

[wdx223_Daniel · Accepted solution]

Calc4 =

AVERAGEX(

    'Table1',

    VAR _d='Table1'[Raw Data 1] * (1 + [parameter1])  VAR _e='Table1'[Raw Data 2] * (1 + [parameter2]) VAR _f=_e/_d VAR _g=_d+_f RETURN _g)

     'Table1'[Raw Data 1] * (1 + [parameter1]))

[sheepworrier]

That is amazing!  Thank you so much.  I never thought I could use VAR inside the AVERAGEX