cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Iterator function with too many calculations

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?

1 ACCEPTED SOLUTION
Super User

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]))

2 REPLIES 2
Super User

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]))

Frequent Visitor

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors