Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
sheepworrier
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:

 

Excel scenario model.png

 

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
wdx223_Daniel
Super User
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]))

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
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]))

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors