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 conditions, specifically when no scopes or all scopes are selected. I expect these conditions to yield identical results in two different line charts, but they do not.
Issue Description:
Line Chart Discrepancy: When no scopes or all scopes are selected, the line chart for "Dynamic Scope Emissions" should mirror the chart for "Avg Asset GHG Emissions (CO2e per $’m)," but the results differ.
Table Totals Mismatch: The total values in my data table for "Dynamic Scopes Emissions" do not match those for "Avg Emissions per Revenue (per Sm)", indicating a potential issue in how totals are computed.
Current Setup:
1. I use a custom table for scope selections called "Scope Options", defined as follows:
Scope Options =
DATATABLE(
"Scope", STRING,
"Selected", BOOLEAN,
{
{"Scope 1", FALSE},
{"Scope 2", FALSE},
{"Scope 3", FALSE}
}
)
2. Dynamic Scopes Emissions Formula: This formula is designed to dynamically sum emissions based on selected scopes and calculate average emissions per million USD of revenue. The formula and its components are intended to adjust based on the slicer's scope selection:
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]),
SUM(ESG_Assets_Data_Pivot[scope1]) + SUM(ESG_Assets_Data_Pivot[scope2]) + SUM(ESG_Assets_Data_Pivot[scope3])
),
ScopeSum
),
TotalRevenue / 1000000,
BLANK()
)
3. Additional Measure for Context: "Average Emissions Per Revenue (per mil)" is used to provide a high-level view of emissions efficiency per revenue, calculated as:
Average Emissions Per Revenue (per mil) =
AVERAGEX(
VALUES(ESG_Assets_Data_Pivot[company_name]),
DIVIDE(
[Total Emissions],
SUM(ESG_Assets_Data_Pivot[revenue_usd]) / 1000000,
0
)
)
Where "Total Emissions" is calculated as follows, summing emissions from all three scopes if any are greater than 0.1:
Total Emissions =
IF(
SUM(ESG_Assets_Data_Pivot[scope1]) + SUM(ESG_Assets_Data_Pivot[scope2]) + SUM(ESG_Assets_Data_Pivot[scope3]) > 0.1,
SUM(ESG_Assets_Data_Pivot[scope1]) + SUM(ESG_Assets_Data_Pivot[scope2]) + SUM(ESG_Assets_Data_Pivot[scope3]),
BLANK()
)
| Asset | Avg Emissions per Revenue (using total emissions) | Dynamic Scope Emissions | scope 1 | scope 2 | scope 3 | revenue | Total Emissions | |
| A | 0.22 | 0.22 | 0 | 0 | 12.97 | $58,286,725.86 | 12.97 | |
| B | 292.95 | 292.95 | 122.22 | 129.13 | 31769.37 | $109,302,981.64 | 32,020.72 | |
| C | 307.78 | 307.78 | 1206 | 1128 | 116905 | $387,421,494.72 | 119,239 | |
| Total | 90.85 | 272.56 | 1328.22 | 1257.13 | 148687.34 | $555,011,202.23 | 151,272.69 | |
Objective:
The "Dynamic Scope Emissions" should accurately reflect the combination of selected scopes and match the "Avg Asset GHG Emissions" when either all scopes or no scopes are selected.
What I've Tried:
Verified slicers are correctly linked.
Checked individual asset data which appears accurate.
Reassessed formulas for any logical or contextual errors.
Request for Assistance: Could anyone help identify why these discrepancies occur or suggest modifications to ensure the DAX formula works as intended? Insights into correcting these values would be immensely appreciated.
Thank you for your time and help!
