March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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):
Solved! Go to Solution.
@Anonymous Please don't worry now! Have managed to get there myself in the end
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 Accept it as the solution to help the other members find it more quickly.
@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...
@Anonymous Please don't worry now! Have managed to get there myself in the end
@jmillsjmills See if this helps:
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.
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 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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
37 | |
29 | |
26 | |
20 | |
16 |