Forum Discussion

user_guddu10's avatar
user_guddu10
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Issues with Matching Dynamic and Static Emissions Calculations in Power BI

Hello Power BI Community, I am facing a challenge in my Power BI report where the dynamic calculations for a measure, scope-based emissions, do not align with average emissions under certain con...
powerbi
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    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 

     

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

When using an iterator you need to force context transition by either calling CALCULATE or by using a measure, in which case CALCULATE is implicitly called.

Try

Dynamic Scopes Emissions =
VAR IsScope1 =
    CONTAINSROW ( VALUES ( 'Scope Options'[Scope] ), "Scope 1" )
VAR IsScope2 =
    CONTAINSROW ( VALUES ( 'Scope Options'[Scope] ), "Scope 2" )
VAR IsScope3 =
    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
VAR TotalRevenue =
    SUM ( ESG_Assets_Data_Pivot[revenue_usd] )
VAR Default = NOT ( IsScope1 || IsScope2
    || IsScope3 ) // True if no scopes are selected
RETURN
    DIVIDE (
        IF (
            Default,
            SUMX (
                VALUES ( ESG_Assets_Data_Pivot[company_name] ),
                CALCULATE (
                    SUM ( ESG_Assets_Data_Pivot[scope1] ) + SUM ( ESG_Assets_Data_Pivot[scope2] )
                        + SUM ( ESG_Assets_Data_Pivot[scope3] )
                )
            ),
            ScopeSum
        ),
        TotalRevenue / 1000000,
        BLANK ()
    )

Average Emissions Per Revenue (per mil) =
AVERAGEX (
    VALUES ( ESG_Assets_Data_Pivot[company_name] ),
    DIVIDE (
        [Total Emissions],
        CALCULATE ( SUM ( ESG_Assets_Data_Pivot[revenue_usd] ) ) / 1000000,
        0
    )
)

Your total measure would be more efficient using variables

Total Emissions =
VAR Scope1 =
    SUM ( ESG_Assets_Data_Pivot[scope1] )
VAR Scope2 =
    SUM ( ESG_Assets_Data_Pivot[scope2] )
VAR Scope3 =
    SUM ( ESG_Assets_Data_Pivot[scope3] )
VAR Result =
    IF ( Scope1 + Scope2 + Scope3 > 0.1, Scope1 + Scope2 + Scope3, BLANK () )
RETURN
    Result