SUMX with Calculated() - Weighted average can't be calculated
Hello all, so for some context, i'm trying to calculate a forecast accuracy with weighted average, meaning when it's all summed up, i want a value that is influenced by how much weight each row has. For the calculation i need to access different key figures, which are defined in a single row called "key figure". For the calculation of the Forecast accuracy i can just use some calculate formulas and it works perfectly fine. Problem is, when i try to make the normal forecast accuracy weighted. I tried to do this with SUMX, but it somehow gives me values that are not possible. I put much time in research and couldn't find anything. I assume that with the SUMX, some filter context of the defined variables get lost (not sure though). Anyway, here's the original DAX formula for the forecast accuracy (not weighted yet). it works as intended: result of this formula. on single row basis this works, but the total (yellow) is not weighted, which is what i try to achieve: So here is the new dax formula, that i created to get the forecast accuracy to be weighted: The result is not possible. On single row bases, the value need to be identical to the above ones, but they aren't. The total doesn't make any sense at all: I was pretty confident that the formula makes sense, but i'm probably missing something. Is this entire thing not possible, because the key figures are in one single colums, just like the values (volume cs) are in the same column? Thanks! 😃 Best regards Nicolas
REALLY hoping for some help on Dynamic Weighted Averages please
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!!
Measure to calculate weighted average of days a stock was held before being sold
Below is a snippet of a facts table 'fTrans' containing historical purchase and sale transactions of shares of stock. [Ticker], [Date], [Sale date related to respective purchase], [Transaction], [Shares] and [Price] are the imported data. [Days] is a measure where [Sale date related to respective purchase] - [Date], hence the time each batch of shares was held before being sold. And [Purchase total] is another measure that computes [Shares] x [Price]. What I need is to calculate the AVERAGE of days each batch of shares was held until its respective SALE transaction took place. As you can see by the expanded snippet below, this is quite a straightforward exercise when a particular sale transaction relates one-on-one to a prior purchase transaction. However things get a bit more complicated as there are instances where a particular sale refers to TWO OR MORE prior purchase transactions, which I highlight some examples below. And to complicate matters further I DO NOT want to calculate a simple straight average by just summing up the days and dividing them up by the number of occurrences. That average calculation needs to be a weighted average using [Purchase total] as the weight basis for each transaction. So I started constructing a couple of temporary tables and then use the second one as the source table to grab the calculated average days for each respective sale date from by writing a third and final RETURN block of the measure code. On the intermediary table 'Supp_Tbl' I calculate the product between [Days] and [Purchase total], which will be used as the weight for each purchase transaction. Then I use 'Supp_Tbl' to calculate the [Weighted average days] and setup the second table 'Days_Tbl' by grouping the [Product of Days and Purchase total] and the [Purchase total] by [Ticker] and [Sale date related to respective purchase] so I can divide them up and then get the result for each sale date. And that's where I get stuck. I do have the results right in front of me and know what needs to be done on the RETURN block in order to get to the measure: Call 'fTrans' table and get each [Ticker]/[Date] combination. Call 'Days_Tbl' and scan it to find each row whose [Ticker]/[Sale date related to respective purchase] combination matches the one from step 1. Grab the respective [WghtdAvgDays] from the corresponding row found on step 2. I just seem not to know how! I've tried several paths such as setting up a third temporary table, INDEX or LOOKUPVALUE but to no avail. Maybe my mind is still too stuck in Excel and prevent me from thinking the DAX way. Any help is immensely appreciated...
Weighted AVG Calculation
Hey all, I have the following data table with many names but I only copied the table with 2 names now: setup.py and path.xyz. (date, name, loc_i, loc_d columns are originally in the table, and AGE, Weight, Weighted AVG are calculated. date name loc_i loc_d AGE Weight Weighted AVG 24/01/22 13:20:06 setup.py 41 0 129.44 33 4271.664375 27/01/22 11:31:23 setup.py 1 1 126.52 1 126.5198727 28/01/22 08:54:20 setup.py 1 1 125.63 1 125.6289352 09/03/22 08:22:06 setup.py 2 2 85.65 2 171.3026389 05/04/22 07:57:04 setup.py 1 0 58.67 1 58.6687037 05/04/22 07:54:23 setup.py 1 0 58.67 1 58.67056713 27/01/22 09:45:36 setup.py 2 2 126.59 2 253.1866667 28/01/22 08:46:19 setup.py 2 2 125.63 2 251.2690046 123.6491 24/01/22 13:20:06 path.xyz 35 0 129.44 26 3365.55375 27/01/22 11:31:23 path.xyz 2 1 126.52 2 253.0397454 28/01/22 08:54:20 path.xyz 1 0 125.63 1 125.6289352 09/03/22 08:22:06 path.xyz 1 0 85.65 1 85.65131944 05/04/22 07:57:04 path.xyz 1 0 58.67 1 58.6687037 05/04/22 07:54:23 path.xyz 1 1 58.67 1 58.67056713 27/01/22 09:45:36 path.xyz 1 1 126.59 1 126.5933333 28/01/22 08:46:19 path.xyz 1 1 125.63 1 125.6345023 24/01/22 13:30:06 path.xyz 1 1 129.44 1 129.4374306 28/01/22 11:31:23 path.xyz 2 2 125.52 2 251.0397454 28/01/22 08:55:20 path.xyz 2 2 125.63 2 251.2564815 123.8763 Here you can see the formulas behind the calculated columns: I would like to create the final calculation in PBI somehow (please note that my data table consists of more than 2 names - thousands). Many thanks in advance!
