Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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]))
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |