Issues with Matching Dynamic and Static Emissions Calculations in Power BI

Hi user_guddu10 

The discrepancy between "Dynamic Scope Emissions" and "Avg Asset GHG Emissions" arises from differences in how totals are calculated in DAX versus row-level data. To fix this:

1. Dynamic Scope Emissions Fix: Ensure the measure calculates totals correctly when no scopes or all scopes are selected. Update it to handle "no scope selected" with a default sum of all scopes.

Dynamic Scopes Emissions =
VAR Default = NOT (CONTAINSROW(VALUES('Scope Options'[Scope]), "Scope 1") ||
                   CONTAINSROW(VALUES('Scope Options'[Scope]), "Scope 2") ||
                   CONTAINSROW(VALUES('Scope Options'[Scope]), "Scope 3"))
VAR ScopeSum = 
    SUM(ESG_Assets_Data_Pivot[scope1]) * IsScope1 +
    SUM(ESG_Assets_Data_Pivot[scope2]) * IsScope2 +
    SUM(ESG_Assets_Data_Pivot[scope3]) * IsScope3
RETURN
    DIVIDE(IF(Default, TotalAllScopes, ScopeSum), TotalRevenue / 1_000_000, BLANK())

• Average Emissions Per Revenue Fix: Ensure consistent totals by aggregating row-level values.

Average Emissions Per Revenue (per mil) =
AVERAGEX(VALUES(ESG_Assets_Data_Pivot[company_name]), [Total Emissions] / RevenuePerMil)
​

• Debug Totals: Use a debugging measure to check aggregated totals against individual rows.

Debug_Total_Emissions = SUMX(VALUES(ESG_Assets_Data_Pivot[company_name]), RowEmissions)
​

• 1. Validate in a Matrix: Test scenarios with all/no scopes selected to ensure calculations align.
     
     By correcting total-level aggregation and validating slicer interactions, both measures will align across visuals and totals.

   

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS