cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
etrietsch Frequent Visitor
Frequent Visitor

Calculating the weighted average gives me incorrect values?

Hi,

 

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

 

MaterialYearLength
ST1976400
ST1956120
PVC19962000
PVC20061200
GIJ1946200
GIJ1936320

 

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating the weighted average gives me incorrect values?

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
6 REPLIES 6
Super User
Super User

Re: Calculating the weighted average gives me incorrect values?

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)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

etrietsch Frequent Visitor
Frequent Visitor

Re: Calculating the weighted average gives me incorrect values?

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...

Super User
Super User

Re: Calculating the weighted average gives me incorrect values?

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

etrietsch Frequent Visitor
Frequent Visitor

Re: Calculating the weighted average gives me incorrect values?

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? 

Super User
Super User

Re: Calculating the weighted average gives me incorrect values?

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: Calculating the weighted average gives me incorrect values?

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |