Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total and Better Average per Category, this one is for a Better Weighted Average per Category.
Power BI's Weighted Average per Category returns something like this:
Value weighted by MonthSort per Month =
VAR __CATEGORY_VALUES = VALUES('Table'[Month])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('Table'[Value]) * SUM('Table'[MonthSort]))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('Table'[MonthSort]))
)
)
It's a pretty unintuitive and complex construction for something so simple. A potentially better way that is more intuitive uses an X aggregator like so:
Better Weighted Average per Category =
VAR __Table = SUMMARIZE('Table',[Month],"Value",SUM('Table'[Value]) * SUM('Table'[MonthSort]))
VAR __Table1 = SUMMARIZE('Table',[Month],"Value", SUM('Table'[MonthSort]))
RETURN
DIVIDE(SUMX(__Table, [Value]), SUMX(__Table1, [Value]))
Watch the video!
eyJrIjoiOGU3NGUxNjQtZWJmZS00YTkxLWEzYzYtNTQ2MmVmZTYyZDE0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Personally, I prefer having only one SUMMARIZE and reusing it.
Better Weighted Average per Category =
VAR _Summary = SUMMARIZE ( 'Table', 'Table'[Month], "@Value", SUM ( 'Table'[Value] ), "@Weight", SUM ( 'Table'[MonthSort] ) )
RETURN
DIVIDE ( SUMX ( _Summary, [@Value] * [@Weight] ), SUMX ( _Summary, [@Weight] ) )
or more generally
VAR _Summary =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Category] ),
"@Value", [ValueMeasure],
"@Weight", [WeightMeasure]
)
RETURN
DIVIDE (
SUMX ( _Summary, [@Value] * [@Weight] ),
SUMX ( _Summary, [@Weight] )
)
@AlexisOlson I actually mentioned using one SUMMARIZE in my video!