- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculating the weighted average gives me inco...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

etrietsch

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
06:29 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
11:34 PM

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

If this post

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

6 REPLIES 6

LivioLanzo

Super User

Re: Calculating the weighted average gives me incorrect values?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
07:07 AM

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

Re: Calculating the weighted average gives me incorrect values?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
08:33 AM

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

Highlighted
##

LivioLanzo

Super User

Re: Calculating the weighted average gives me incorrect values?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
09:05 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
09:54 AM

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

Super User

Re: Calculating the weighted average gives me incorrect values?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
10:52 AM

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! **

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
11:34 PM

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

If this post

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