REALLY hoping for some help on Dynamic Weighted Averages please

jmillsjmills — Wed, 18 Oct 2023 15:56:22 GMT

Hi there!

I'm very new to Power BI and am really hoping for some help. I'm attaching two versions of the PBIX and an Excel showing the general workings that 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

Essentially I have a portfolio with a list of stock weights. There are multiple rows per stock, duplicating the stock weight data. Each unique row is for various metrics on the stocks (ROE, beta etc). For each stock, I need to calculate a weighted average for a metric. When the metric isn't available for a stock, it should automatically rebalance (to 100% in total) the weights for the stocks where the data is available.

Crucially, I'm hoping the weighted average can be dynamic. i.e. when users filter clicking within the dashboard, the weighted average recalculates. So if we filtered to four 5% stocks that total 20% of portfolio, because it's filtered this would all rebalance to 100% and the 5% stocks all become 25% for example.

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 first of these effects (filtering out and rebalancing stocks based on data availablility).

So the Excel shows arrives at the correct aggregate weighted averages for each metric based on the sum of [RebalancedWeight]*[Value] rows for a metric. You can see these correct weighted averages in Column I.

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

In the first file, everything calculates correctly at the aggregate level, and therefore produces correct weighted average metrics ("WA-Metric" column). HOWEVER - it cannot handle user-applied filters (the measure is only using ALLEXCEPT).
In the second PBIX, the "FlexibleGroupWeight" measure can handle the user-filtering process.. You click the pie chart to filter on a particular sector, and the number in the first column correctly drops to just the % of the portfolio we are filtered to. The rebalancing should then work off this 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? I think that it what would finally get me there!

THANK YOU SO SO MUCH IN ADVANCE FOR ANY HELP!!

Re: REALLY hoping for some help on Dynamic Weighted Averages please

123abc — Thu, 19 Oct 2023 06:16:50 GMT

It seems like you're working on a complex scenario in Power BI, where you want to calculate dynamic weighted averages for various metrics, and you want these calculations to adapt when users apply filters to the data. I'll try to provide some guidance on how you can achieve this.

In your description, you mentioned two Power BI files, one using the "ALLEXCEPT" function and the other using "ALLSELECTED." Let's combine these two approaches to create a dynamic weighted average that adjusts with user filters.

Data Model Preparation:
Ensure that your data model in Power BI is well-structured with relationships between tables and the necessary columns for calculations.
Measures:
You'll need to create a set of measures to calculate weighted averages for different metrics. For this example, I'll use the "FlexibleGroupWeight" measure and adjust it to handle user filters.

Weighted Average =
DIVIDE(
SUMX(
FILTER(
ALLSELECTED('StockTable'),
ISBLANK([MetricValue]) = FALSE
),
'StockTable'[Weight] * [MetricValue]
),
SUMX(
FILTER(
ALLSELECTED('StockTable'),
ISBLANK([MetricValue]) = FALSE
),
'StockTable'[Weight]
)
)

This measure calculates a weighted average for the selected data after applying user filters while considering only rows where "MetricValue" is not blank.
Visuals:
Use this new "Weighted Average" measure in your visuals to display dynamic weighted averages.

With this approach, when users apply filters to your data, the "Weighted Average" measure will adapt to the filtered data, and the weighted average calculations will be dynamic. It will also automatically rebalance when some stocks are excluded due to missing data.

Remember to replace 'StockTable' and [MetricValue] with the actual table and column names from your data model.

This approach should help you achieve your desired dynamic weighted average calculations that adapt to user filters.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

topic REALLY hoping for some help on Dynamic Weighted Averages please in DAX Commands and Tips

REALLY hoping for some help on Dynamic Weighted Averages please

Re: REALLY hoping for some help on Dynamic Weighted Averages please