Forum Discussion
Weighted Average with calculated weights
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 con...
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)))