Original Measures,
_CombinedWeightedPercentage =
IF(
HASONEVALUE(Volumes[Region]),
1,
-- Case when a single region is selected, calculate weighted percentage within the region
DIVIDE(
SUM(Volumes[Volume]),
CALCULATE(
SUM(Volumes[Volume]),
ALLEXCEPT(Volumes, Volumes[Region])
)
)
and,
_TotalWeightedCOP =
IF(
HASONEVALUE('CAD$,T,Ha'[Region]),
-- Case when a single region is selected: calculate weighted cost per sourcing area
SUMX(
SUMMARIZE(
'CAD$,T,Ha',
'CAD$,T,Ha'[Region],
'CAD$,T,Ha'[Sourcing Area],
'CAD$,T,Ha'[Growing Season],
"AvgInputCost", AVERAGE('CAD$,T,Ha'[Total Input Cost]),
"WeightedPercentage", [_CombinedWeightedPercentage]
),
[AvgInputCost] * [WeightedPercentage]
),
-- Case when all regions are selected: calculate weighted cost per region
SUMX(
SUMMARIZE(
'CAD$,T,Ha',
'CAD$,T,Ha'[Region],
"RegionAvgInputCost",
IF(
-- Check if region has only one variety
COUNTROWS(VALUES('CAD$,T,Ha'[Variety])) = 1,
-- Case 1: One variety in the region
AVERAGEX(
VALUES('CAD$,T,Ha'[Sourcing Area]),
AVERAGE('CAD$,T,Ha'[Total Input Cost])
),
-- Case 2: Multiple varieties in the region
SUMX(
VALUES('CAD$,T,Ha'[Sourcing Area]),
AVERAGEX(
VALUES('CAD$,T,Ha'[Variety]),
'CAD$,T,Ha'[_CurrencyCOP]
)
)
),
"RegionWeight", LOOKUPVALUE(Weights[Weight Region], Weights[Regions], 'CAD$,T,Ha'[Region])
),
[RegionAvgInputCost] * [RegionWeight]
)
)
I’m working with a large dataset that tracks the Cost of Production (COP) over the last 10 years, broken down by region, sourcing area, and variety.
I want to visualize the COP as a weighted average, where:
The weights come from production volumes, which differ by sourcing area.
Within a sourcing area, there can be multiple varieties, each with its own volume, so the weighted average must also account for the volume of each variety.
I created two DAX measures that calculate the global weighted average COP, using predefined weights from a [Weights] table. This works correctly for the overall (global) calculation.
However, the issue arises when I select only a few sourcing areas (either:
In these cases, Power BI continues to apply the global weights from the [Weights] table instead of dynamically recalculating the weights based on the actual selected sourcing areas and their volumes.
As a result, the weighted COP is incorrect — it uses static global weights rather than adjusting the weights to the filtered selection. What I need is for Power BI to:
To solve this I designed two new measures,
_SelectedWeight =
VAR TotalVolumeSelectedSourcingAreas =
CALCULATE(
SUM(Volumes[Volume]),
ALLSELECTED(Volumes[Sourcing Area])
)
VAR TotalVolumeSelectedRegions =
CALCULATE(
SUM(Volumes[Volume]),
ALLSELECTED(Volumes[Region])
)
VAR IsOneRegionSelected = HASONEVALUE(Volumes[Region])
VAR IsOneSourcingAreaSelected = HASONEVALUE(Volumes[Sourcing Area])
VAR NumberOfSelectedRegions =
CALCULATE(
COUNTROWS(VALUES(Volumes[Region])),
ALLSELECTED(Volumes[Region])
)
VAR TotalNumberOfRegions =
CALCULATE(
COUNTROWS(ALL(Volumes[Region]))
)
VAR AreAllRegionsSelected = NumberOfSelectedRegions = TotalNumberOfRegions
RETURN
SWITCH(
TRUE(),
-- Case 1: Only one sourcing area selected
IsOneSourcingAreaSelected, 1,
-- Case 2: One region selected without specifying sourcing areas
IsOneRegionSelected && NOT IsOneSourcingAreaSelected,
DIVIDE(
SUM(Volumes[Volume]),
TotalVolumeSelectedRegions
),
-- Case 3: NOT all regions selected, dynamic calculation based on selected sourcing areas
NOT AreAllRegionsSelected,
DIVIDE(
SUM(Volumes[Volume]),
TotalVolumeSelectedSourcingAreas
),
-- Case 4: All regions selected, use fixed region weights
AreAllRegionsSelected,
LOOKUPVALUE(
Weights[Weight Region],
Weights[Regions],
SELECTEDVALUE('CAD$,T,Ha'[Region])
)
)
and,
_COPWeighted =
SUMX(
ADDCOLUMNS(
VALUES('CAD$,T,Ha'[Sourcing Area]),
"WeightedSourcingAreaCOP",
VAR SourcingAvg = AVERAGEX(
VALUES('CAD$,T,Ha'[Variety]),
'CAD$,T,Ha'[_CurrencyCOP]
)
RETURN
SourcingAvg * [_SelectedWeight] -- Dynamically adjusted now
),
[WeightedSourcingAreaCOP]
)
However, the values produced now are too large. I am not sure where the issue lies.
Has anyone handled a similar issue or found a robust way to dynamically adjust weights in Power BI measures depending on the filters (region, sourcing area, variety) applied?
This is my first dashboard, and by far the hardest issue I have had to dealt with.
Thanks
