SumProduct in DAX - Weighted Average

[jmillsjmills]

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]))

        )

    )

[Anonymous]

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.

[jmillsjmills]

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

[jmillsjmills · Accepted solution]

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

[Greg_Deckler]

@jmillsjmills See if this helps:

[jmillsjmills]

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.

Excel workings

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

PBIX where the weight column correctly rebalances when user applies filters, but then weighted avera...

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.