Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
09-29-2022 01:29 AM - last edited 05-16-2023 11:29 AM
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!