topic Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX in DAX Commands and Tips

Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

user_guddu10 — Tue, 20 May 2025 03:24:32 GMT

Hi all,

I'm running into a performance issue with a DAX measure in Power BI and would appreciate some help.

🔹 Context:

I’m working with a combined table called benchmarking_combined_data that merges internal ESG company data and external listed company data.

Each row includes:

company_name
report_year
survey_year (blank for listed companies)
revenue_usd
scope1, scope2, scope3

🔹 Source Selection Logic:

To control which data is shown in visuals and calculations, I use a slicer from a separate table SourceSelector[Source] with values:

ESG
Listed
Both

In Power Query, I manually added a Source column to both original datasets:

ESG data: Source = "ESG"
Listed data: Source = "Listed"

These tables were then appended into benchmarking_combined_data, and the Source column is used to dynamically filter data based on the slicer selection (via SELECTEDVALUE(SourceSelector[Source]) in measures).

🔹 Scope Selection Logic:

I also have a ScopeSelector[ScopeName] slicer to let users select:

Scope 1
Scope 2
Scope 3

Emissions are computed based on which scopes are selected.

🔍 The Problematic Measure:

This measure calculates the average emissions per revenue (per $M) across companies and years. It filters by source and scope, computes per-row ratios, and averages them (excluding ratios < 0.1).

Combined Benchmark Avg Emissions Per Revenue (per mil) = VAR _SelectedSource = SELECTEDVALUE(SourceSelector[Source], "ESG") RETURN AVERAGEX( SUMMARIZE( FILTER( benchmarking_combined_data, SWITCH( _SelectedSource, "ESG", benchmarking_combined_data[Source] = "ESG", "Listed", benchmarking_combined_data[Source] = "Listed", "Both", TRUE() ) ), benchmarking_combined_data[company_name], benchmarking_combined_data[report_year], "Total Emissions B", [Combined Benchmark Total Emissions], "Total Revenue B", [Combined Benchmark Revenue by Report Year] ), VAR Emissions = [Total Emissions B] VAR Revenue = [Total Revenue B] VAR PerMillion = DIVIDE(Emissions, Revenue / 1000000, 0) RETURN IF(PerMillion >= 0.1, PerMillion, BLANK()) )

⚙️ Supporting Measures:

[Combined Benchmark Total Emissions]

Adds together the relevant scopes selected in the slicer:

Combined Benchmark Total Emissions = VAR _SelectedScopes = VALUES(ScopeSelector[ScopeName]) VAR _Scope1 = IF(CONTAINS(_SelectedScopes, ScopeSelector[ScopeName], "Scope 1"), [Combined Benchmark Total Scope1 Latest Survey Year], 0) VAR _Scope2 = IF(CONTAINS(_SelectedScopes, ScopeSelector[ScopeName], "Scope 2"), [Combined Benchmark Total Scope2 Latest Survey Year], 0) VAR _Scope3 = IF(CONTAINS(_SelectedScopes, ScopeSelector[ScopeName], "Scope 3"), [Combined Benchmark Total Scope3 Latest Survey Year], 0) RETURN _Scope1 + _Scope2 + _Scope3

Each scope submeasure:

For ESG: uses the latest survey_year per company per report year
For Listed: uses values directly from the report year

[Combined Benchmark Revenue by Report Year]

Returns revenue using similar logic (not dependent on survey year):

Combined Benchmark Revenue by Report Year = VAR _SelectedSource = SELECTEDVALUE(SourceSelector[Source], "ESG") VAR _BaseValueESG = SUMX( VALUES(benchmarking_combined_data[report_year]), VAR MaxSurveyYear = CALCULATE( MAX(benchmarking_combined_data[survey_year]), benchmarking_combined_data[Source] = "ESG", ALLEXCEPT(benchmarking_combined_data, benchmarking_combined_data[company_name], benchmarking_combined_data[report_year]) ) RETURN CALCULATE( MAX(benchmarking_combined_data[revenue_usd]), FILTER( benchmarking_combined_data, benchmarking_combined_data[survey_year] = MaxSurveyYear && benchmarking_combined_data[Source] = "ESG" ), ALLEXCEPT(benchmarking_combined_data, benchmarking_combined_data[company_name], benchmarking_combined_data[report_year]) ) ) VAR _BaseValueListed = CALCULATE( SUM(benchmarking_combined_data[revenue_usd]), benchmarking_combined_data[Source] = "Listed" ) RETURN SWITCH( _SelectedSource, "ESG", _BaseValueESG, "Listed", _BaseValueListed, "Both", _BaseValueESG + _BaseValueListed )

🧪 Problem:

When I change either the SourceSelector or ScopeSelector, this emissions/revenue measure takes up to 5 minutes to refresh in my matrix visual.

If I remove this measure, everything runs instantly.

❓ My Question:

How can I optimize this AVERAGEX-based measure so that:

It retains the per-company emissions/revenue logic
It respects both slicers (SourceSelector and ScopeSelector)
It still excludes ratios under 0.1
It performs much faster for interactive use?

Would restructuring or precalculating any parts of the logic help?

Any performance-focused advice is much appreciated — thank you!

Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

pravinW007 — Tue, 20 May 2025 06:10:08 GMT

Hi user_guddu10 ,

You can use Unpivot other columns transformatation on scope columns so that you will get two column scope and value. Scope will have values like scope 1, scope 2, scope 3. You can use this in dropdown to dynamically select scope and use simple measure avg(value). This approach is more efficient than yours.
Thanks,
Pravin Wattamwar

Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

user_guddu10 — Thu, 22 May 2025 00:37:18 GMT

Hi Pravin,

Thanks a lot for the suggestion — unpivoting the scope columns is definitely a great approach in many cases and can simplify the model when you only need to work with one scope at a time.

In my case, though, I need to allow users to select multiple scopes at once (e.g., Scope 1 + Scope 2 + Scope 3) using a slicer, and the measure then dynamically adds those selected scopes together. I also have source-specific logic — for example, ESG data requires selecting the value for the latest survey year, whereas Listed data uses the value directly from the report year. That logic is applied differently depending on the selected source and scope.

With an unpivoted structure, it becomes quite difficult to apply this kind of dynamic multi-scope and per-source logic efficiently. So I’m sticking with the current wide format (scope1, scope2, scope3 as separate columns), which better supports my model requirements.

But I really appreciate your input — it’s a solid approach for models that don’t need this level of complexity.

Thanks again

Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

pravinW007 — Thu, 22 May 2025 07:00:00 GMT

Hi user_guddu10
I also work on Climate and ESG report. We follow unpivot approach and then provide scope slicer so that user can select any scope which he wants. Once you unpivot the scope columns you will get scope 1, scope 2 so on in rows and provide multiselect slicer option to users so that they can choose multiple values.

If you still need any help create a sample mock data and expected output. Definately we can figure out simple approach rather than making it complicated.

Thanks,
Pravin Wattamwar

Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

v-kathullac — Fri, 23 May 2025 11:45:03 GMT

Hi user_guddu10 ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya

Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

user_guddu10 — Sun, 25 May 2025 23:32:50 GMT

Thank you for your reply again! Posting mock data below:

Company	Report year	Survey year	Revenue	Scope1	Scope2	Scope3	Source
Alpha Corp	2022	2023	100,000,000	500	300	200	ESG
Alpha Corp	2022	2024	(blank)	450	290	210	ESG
Alpha Corp	2023	2024	120,000,000	550	320	250	ESG
Beta Ltd	2022	2023	85,000,000	400	200	100	ESG
Beta Ltd	2022	2024	88,000,000	410	210	110	ESG
Gamma Inc	2023	2024	75,000,000	300	150	120	Listed

🔍Key Issues Shown in This Mock Data

Duplicate report_year for same company (e.g., Alpha Corp, 2022)
- Two survey years: 2023 (has revenue), 2024 (latest, but missing revenue)
If Power Query only keeps latest survey_year, you lose the row with revenue (2023), and totals are wrong
Emissions values also vary, so you may lose higher-quality scope data when selecting only by survey year

Re: Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

pravinW007 — Mon, 26 May 2025 08:39:13 GMT

Hi user_guddu10 only upivot Scope related columns. Post that you will have duplicates of revenue and you can handle that using Summarize and SUMx..

Thanks,

Pravin Wattamwar