Calculating the weighted average gives me incorrect values?

[etrietsch]

Hi,

When using the "New quick measure" button to calculate a weighed for the following example, I get a formula that doesn seem right.

Material	Year	Length
ST	1976	400
ST	1956	120
PVC	1996	2000
PVC	2006	1200
GIJ	1946	200
GIJ	1936	320

When using Age, Year and Material as input, the generated formula is:

Age weighted by Length per Material = 
VAR __CATEGORY_VALUES = VALUES('Test data'[Material])
RETURN
	DIVIDE(
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES);
			CALCULATE(SUM('Test data'[Age]) * SUM('Test data'[Length]))
		);
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES);
			CALCULATE(SUM('Test data'[Length]))
		)
	)

But the results for the materials are not correct however. But when using a SUMX() instead of SUM()*SUM() the results ARE correct:

Age weighted by Sum Length per Material = 
VAR __CATEGORY_VALUES = VALUES('Test data'[Material])
RETURN
	DIVIDE(
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES);
			CALCULATE(SUMX('Test data';'Test data'[Age]*'Test data'[Length]);NOT(ISBLANK('Test data'[Age]));NOT(ISBLANK('Test data'[Length])))
		);
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES);
			CALCULATE(SUM('Test data'[Length]);NOT(ISBLANK('Test data'[Age]));NOT(ISBLANK('Test data'[Length]))
		)
	))

Does this mean I 'm doing something wrong or is the autogenerated formula not correct?

[v-shex-msft · Accepted solution]

Hi @etrietsch,

It seems like the normal measure calculate issue on total/summary level, maybe you can take a look at following link about measure total issue:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

[LivioLanzo]

Hi @etrietsch

the first formula does indeed not look correct, calculation needs to be at per row level;

in fact

( 1 X 10 ) + ( 5 X 3 ) does not equal ( 1+ 5 )  X ( 10 + 3)

[etrietsch]

Hi @LivioLanzo, so does this mean that the autogen formula is incorrect and that this is a bug? As I now have to manually correct the formula...

[LivioLanzo]

it does not look too correct to me, if you post a dataset and expected results we can make a new formula

[etrietsch]

Hi @LivioLanzo In my question I already posted the correct version of the formula: this one works as expected. But I was wondering why the 'out of the box' formula is incorrect? Is it a bug or is do I use the wizard incorrectly? 

[LivioLanzo]

Hi @etrietsch,

the first formula is incorrect because it is doing a multiplication of the sum instead of a sum of the multiplication which is what the second formula is doing