Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jmillsjmills
Helper III
Helper III

SumProduct in DAX - Weighted Average

WeightFigureCategoryProduct
0.310Category1A
0.220Category1B
0.14Category1C
0.414Category1D
0.312Category2A
0.215Category2B
0.19Category2C
0.4nullCategory2D

 

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]))
        )
    )
1 ACCEPTED SOLUTION

@v-binbinyu-msft Please don't worry now! Have managed to get there myself in the end

View solution in original post

5 REPLIES 5
v-binbinyu-msft
Community Support
Community Support

Hi @jmillsjmills ,

Please try below steps:

1. below is my test table

vbinbinyumsft_0-1697697636652.png

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

vbinbinyumsft_1-1697697719385.png

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.

@v-binbinyu-msft 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_0-1697809103640.png

 

@v-binbinyu-msft Please don't worry now! Have managed to get there myself in the end

Greg_Deckler
Super User
Super User

@jmillsjmills See if this helps:


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors