Better Weighted Average per Category 09-29-2022 01:29 AM - last edited 05-16-2023 11:29 AM

[Greg_Deckler]

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

[AlexisOlson]

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] )
    )

[Greg_Deckler]

@AlexisOlson I actually mentioned using one SUMMARIZE in my video!

https://youtu.be/jlPTnELGbPg