Performance issue with AVERAGEX and SUMMARIZE in Power BI DAX

[user_guddu10]

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!

[v-kathullac]

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

[user_guddu10]

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

[pravinW007]

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

[user_guddu10]

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

1. Duplicate report_year for same company (e.g., Alpha Corp, 2022)

   • Two survey years: 2023 (has revenue), 2024 (latest, but missing revenue)

2. If Power Query only keeps latest survey_year, you lose the row with revenue (2023), and totals are wrong

3. Emissions values also vary, so you may lose higher-quality scope data when selecting only by survey year

[pravinW007 · Accepted solution]

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

[pravinW007]

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