cancel
Showing results for
Did you mean:
Highlighted
etrietsch 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.

 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?

1 ACCEPTED SOLUTION

Accepted Solutions 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

## Re: Calculating the weighted average gives me incorrect values?

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

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

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

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

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

## 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: | |