Forum Discussion
Weighted Average with calculated weights
So, in the example provided what should the Weighted Average numbers be? 50,000, 60,000 and 70,000 or something different and if different, what?
The weighted average for category A should be the weights of each item [(6 * 50,000) + (5 * 60,000)] / total days in category (11) = 54,545. The weighted average is not filled in yet in my screen shots because I can't figure out the calculation. The blue fields filled in are what I am able to do so far. Having the days in period respond to the slicer selections is throwing me for a bit of a loop.
Thank you for the clarifying question, and any help from the community would be much appreciated :)
Hello again PBI community, thank you for the responses to my last post. I will test and confirm answers on Monday :)
I have quite a good problem to solve. I have 3 tables.
The first table contains IDs, start and end dates, and a value.
The second and third tables are date tables, one linked to the start date, and one linked to the end date. (The purpose of this is so users can select any dates in the date slicer, not just the ones that happen to have data in the first table).
I have it set up so the user selects a range based on 2 slicers, the first range is a greater than date based on the end date, and the second is a less than date based on the start date. The purpose here is so the user can select any date range, and find the ID's that were 'live' in that range. Live means that if the selected dates are [end date>02/05/2018] and [start date<02/10/2018] all ID's that have overlap in this period (the whole period or part of the period) will show up.
The period created is 02/05/2018 to 02/10/2018. So any combination of being live for 1 day to all days in the selected period would show up.
Here is an example of the tables, and where the problem comes in.
Table 1:
When the range of 02/05/2018 - 02/10/2018 is selected here is where I can get to:
*Note: the days are inclusive so the entire period of being 'live' is 6 days.
The trouble is that the days in the period selected is a measure based on the slicer selections, the weights are also a measure, and I cannot divide the weights by the sum of the days to get the weighted amount. This is really where I need help.
I included a category because there is a heirarchy of categories, so weighted averages within categories is what I need. This is also just an example. There are hundreds of categories, and tens of thousands of ID's, so no formulas with non-dynamic grouping are feasible.
If further explanations are needed then I will definately put some more color around the problem.
OK, got it. Can you post your current measure/column formulas?
OK, here is what I came up with. See attached "Table15 2" and the 2 associated calendar tables. Page 11.
Here are the measures I created:
Days in Period = VAR __min = MAX('Table15_2_Calendar1'[Date]) VAR __max = MAX('Table15_2_Calendar2'[Date]) VAR __g1 = MAX([Start]) VAR __g2 = MAX([End]) VAR __table = GENERATESERIES(__g1,__g2,1) RETURN COUNTROWS(FILTER(__table,[Value]>=__min && [Value] <= __max))Weights = [Days in Period] * MAX([Number])
Weighted Average = VAR __category = MAX([Category]) VAR __table = ADDCOLUMNS(ALL('Table15 2'),"__DaysInPeriod",[Days in Period],"__Weights",[Weights]) VAR __table1 = FILTER(__table,[Category] = __category) VAR __days = SUMX(__table1,[__DaysInPeriod]) RETURN SUMX(__table1,[__Weights])/__daysStill not sure about your 2/30/2018 date, that seems wrong to me...
Thank you for the suggestions Greg, and I apologize for the ambiguous data earlier. I tried to simplify the issue, and I think that was confusing. Here is a screen of the actual data. I tried your solution and it seems like it will work with a few tweaks. The problem I am having is that the GENERATESERIES function cannot be blank, and there will be more rows with no series generated than with series since the user will only be looking at short time frames at a time.
Here is how I tried to modify the DAX, but I can't quite figure it out even for step 1, the days in period measure.
Measure - Days in Period = VAR SlicerMat = CALCULATE(MIN('DateTable.1'[Date.1]), ALLSELECTED('DateTable.1'[Date.1])) VAR SlicerSettle = CALCULATE(MAX('DateTable.2'[Date.2]), ALLSELECTED('DateTable.2'[Date.2])) VAR Maturity = SUMX(MONEY_MARKETS,MAX(MONEY_MARKETS[Fin. Transaction.Fin. Transaction Level 01.Start of Term (Key)])) VAR Settlement = SUMX(MONEY_MARKETS,MAX(MONEY_MARKETS[Fin. Transaction.Fin. Transaction Level 01.End of Term (Key)])) VAR Table_Days = GENERATESERIES(Maturity, Settlement, 1) Return COUNTROWS(FILTER(Table_Days, [Value] >= SlicerMat && [Value] < SlicerSettle))I think the missing piece is that it should only return something if it fits the criteria. Any ideas?
P.S. I love the 'Measure Totals, The Final Word' post. VERY helpful!
Can you just do this?
Weighted Average = VAR __category = MAX([Category]) VAR __table = ADDCOLUMNS(ALL('Table15 2'),"__DaysInPeriod",[Days in Period],"__Weights",[Weights]) VAR __table1 = FILTER(__table,[Category] = __category) VAR __days = SUMX(__table1,[__DaysInPeriod]) RETURN IF(ISBLANK([Days in Period]),BLANK(),SUMX(__table1,[__Weights])/__days)Attached updated with a row that is "out-of-bounds".