- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: SumProduct in DAX - Weighted Average

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

SumProduct in DAX - Weighted Average

10-17-2023
08:48 AM

Weight | Figure | Category | Product |

0.3 | 10 | Category1 | A |

0.2 | 20 | Category1 | B |

0.1 | 4 | Category1 | C |

0.4 | 14 | Category1 | D |

0.3 | 12 | Category2 | A |

0.2 | 15 | Category2 | B |

0.1 | 9 | Category2 | C |

0.4 | null | Category2 | D |

I'm very new to DAX and trying to get an understanding of how to implement a Weighted Average.

Category1 is clearly straight-forward: (0.3*10)+(0.2*20)+(0.1*4)+(0.4*14) = 13

I was hoping to use the quick measure interface to calculate a weighted average of the "Figure" column for Category1 and Category2 respectively.

Even how to do Category1 I'm not clear on. In the interface for "Weighted average per category", I select:

Base value = "Average of Figure"

Weight value = "Sum of Weight"

Category = "Category"

But my issue is then that the DAX takes the "Average of Figure" for ALL Figures data for Category1 or ALL of Category2. Then multiplies that same average by each weight... So you'd get a "Weighted Average" of 12 for Category1 when (as above) it should be 13.

But then for example in Category2 where the "Value" column has one null, I need the weight column to automatically rebalance. So the calculation ignores the 0.4 weight and rebalances the rest to total 1 (i.e. 100%...)

The nulls are excluded from my dataset anyway. The point is just: that it should always proportionately rebalance the weights in each category to 1 (or 100%) to then calculate a weighted average using the weights for each individual product. Does that make sense?

All help is very very much appreciated! Thanks

Code-wise what i have so far (what isn't working):

Average of Figure weighted by Weight per Category =

VAR __CATEGORY_VALUES = VALUES('Table'[Category])

RETURN

DIVIDE(

SUMX(

KEEPFILTERS(__CATEGORY_VALUES),

CALCULATE(

AVERAGE('Table'[Figure])

* SUM('Table'[Weight])

)

),

SUMX(

KEEPFILTERS(__CATEGORY_VALUES),

CALCULATE(SUM('Table'[Weight]))

)

)

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-20-2023
08:51 AM

@Anonymous Please don't worry now! Have managed to get there myself in the end

5 REPLIES 5

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-18-2023
11:42 PM

Hi @jmillsjmills ,

Please try below steps:

1. below is my test table

2. create a measure with below dax formula

```
Measure =
VAR cur_category =
SELECTEDVALUE ( 'Table'[Category] )
VAR tmp =
FILTER ( ALL ( 'Table' ), [Category] = cur_category && [Figure] <> BLANK () )
RETURN
SUMX ( tmp, [Weight] * [Figure] )
```

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,

Community Support Team_Binbin Yu

If this post * helps*, then please consider

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-20-2023
06:41 AM

@Anonymous On the first part, the weighted average is clearly correct at 13.0 as per my written example. However I need the weightings to rebalance to 100% dynamically, based on both whether there's any data in the metric column, or if there's any filters applied.

So for the Category 2 calc the 30%, 20% and 10% weights in products A, B and C become 50%, 33.3% and 16.7% respectively (because Product D is excluded due to lack of data).

So the weighted average calc (without filters applied) should be (0.5*12)+(0.33*15)+(0.167*9)=12.453

However, I'm hoping that it can also handle the user filtering the dashboard. Let's say they select only two products A and B (maybe the belong to another grouping that has been clicked on a pie chart in the dashbaord, or these two products have been directly selected).

But if these two only are selected, it should then rebalance again to 100% before calculating weighted average. So the 30% and 20% weights in product A and B become 60% and 40% respectively.

Therefore in this case the weighted averages would be:

Category1 = (0.6*10)+(0.4*20)=14

Category2 = (0.6*12)+(0.4*15)=13.2

Do you see what I'm getting at? Pardon the slightly different labels being used for the columns, but as it happens I've made good progress in the below linked PBIX.. I just can't get to the very last step.

https://drive.google.com/file/d/1XRZTiwFT0dKbZCifO2Q6fzzpHZuhwurd/view?usp=share_link

- "FlexibleGroupWeight" is working as it should, showing the total % the selected rows represent of total weights

- "Rebalanced" then successfully rebalances the original weight ("NameWeight") based on "FlexibleGroupWeight", such that the sum of this column = 100% as it should

- Then "ValueWeighted" is successfully rebalancing the original statistic ("NameValue") by the "Rebalanced" weight..

- so all I need is the final column to display the total of the "ValueWeighted" column (identical in each row)...

.. but because it's a measure rather than just a column it seems to be causing me issues. In the screenshot below, for example, it should simply sum 10.4469 + 1.3619 + -0.0583 + 0.7638 = 12.5131... But I'm getting 0.4 in each column at moment!?

I would REALLY appreciate your help here to get the final step sorted! I feel I am so close to having the perfect dynamic weighted average calculator...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-20-2023
08:51 AM

@Anonymous Please don't worry now! Have managed to get there myself in the end

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-17-2023
09:03 AM

@jmillsjmills See if this helps:

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-18-2023
08:42 AM

Hi Gary - this was useful thanks, and I've made progress. But I'm still stuck. I'm really hoping you can please help me so I'm attaching two versions of the PBIX and an Excel showing the general workings I'm aiming for.

PBIX where overall weighted average is correct but it can't rebalance where user applies filters

**NOTE: only difference between these two files is the FlexibleGroupWeight measure **

Hopefully you can see what I'm getting at - I've included an Excel workbook to show how I would use SUMIFs to achieve the same effect.

- You have a range of statistical metrics for stocks (these labelled in the "Attribute" column, with "Value" being the given statistic for each given stock).
- Not all stocks necessarily have data available, so we need to rebalance individual stock weights to whatever total % has the metric available. If only 90% of portfolio has data for a particular metric, a 9% weight stock (with available data) becomes a 10% rebalanced weight for the weighted average for example.

So the Excel shows how this calculation works with SUMIFs, arriving at the correct aggregate weighted averages for each metric based on the sum of [RebalancedWeight]*[Value] for a metric.

In the Power BI, it all hinges on the measure called "FlexibleGroupWeight" measure.

- In the first file, it calculates correctly at the aggregate level, and therefore produces correct weighted average metrics ("WA-Metric") but it cannot handle user-applied filters (the measure is only using ALLEXCEPT).
- Whereas in the second PBIX the "FlexibleGroupWeight" measure can handle the user-filtering process.. If you click the pie chart to filter on just Energy stocks for example, it drops to 17% because this is the total % weight in energy companies. The rebalancing should then work off this much smaller total to do a weighted average in that sector only. However - suddenly the actual weighted average metrics are WAY out and it looks as though they become the sum of all the individual stock metric values..

In latter case my measure uses ALLSELECTED whereas first case uses ALLEXCEPT... Is there some way of combining these two formulas please to get the desired effect?

THANK YOU SO SO MUCH IN ADVANCE!!

I feel like it has something to do with being able to use ALLEXCEPT() and ALLSELECTED() together somehow.

Announcements

Check out the October 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors

User | Count |
---|---|

30 | |

16 | |

14 | |

14 | |

9 |