Forum Discussion
Weighted Average with calculated weights
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])/__days
Still 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".
The trouble is in this measure
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))In my actual data when I do the equivalent of 'VAR __table = GENERATESERIES(__g1,__g2,1)' there is a message that says Generate Series cannot be blank. Any suggestions?
OK, this seems like it is getting a little hacky at this point, probably could use a refactoring but try this:
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 __g1a = IF(ISBLANK(__g1),DATE(1899,12,30),__g1) VAR __g2a = IF(ISBLANK(__g2),DATE(1899,12,30),__g2) VAR __table = GENERATESERIES(__g1a,__g2a,1) RETURN IF(ISBLANK(__g1a) || ISBLANK(__g2a),BLANK(),COUNTROWS(FILTER(__table,[Value]>=__min && [Value] <= __max)))