Weighted Average by Distinct Category

[poweruser124]

Hello PBI Community,

I'm fairly new to DAX and am trying to write a DAX formula to calculated weighted average by distinct category as follows:

The table structure is this:

Row ID (Key)__	Category___	Place Name__	Total Sample__	Average Size within Sample
123	A	Indiana	1400	14
232	A	Ohio	1400	14
111	B	New Jersey	300	30

Values for Total Sample and Average Size within Sample columns are grouped by category (i.e each rows with the same category will have the same value for those 2 columns). I am trying to calculate the folowing weighted average by distinct category.  For the data above, the result I would want is below:

[ (1400 x 14) + (300 x 30) ] / (1400+300) = 16.82 (This is the desired result). In a slicer if Place Name Indiana, Ohio and New Jersey are selected, I would want this value. The result will also be same if only Indiana and New Jersey are selected. If only Indiana and Ohio are selected (places with same category), I would not want to do any aggregation and only display the calucation as 14.

I have created a DAX formula below that does not take into account the distinct category which gives the below:

DIVIDE(
SUMX('Table','Table'[Total Sample]*'Table'[Average Size within Sample]),
SUM('Table'[Total Sample]))

Translates to:

[(1400 x 14) +(1400 x 14) + (300 x 30)] / [ (1400+1400+300)] = 15.54 (This is not the desired result)

Any help that can be suggested to modify the formula to get the desired result will be appreciated.

Thank you so much.

[poweruser124]

Thank you @amitchandak , this worked. Much appreciated!

[amitchandak · Accepted solution]

@poweruser124 , Try a measure like

Var _tab= Summarize(Table,[Total Sample],Table[Average Size within Sample])
return
divide(Sumx(_tab, [Total Sample] * Table[Average Size within Sample]), Sumx(_tab, [Total Sample]))