Within a viz, I want to remove all context from a table except a few fields and calculated weighted average (within region/subregion/facility, find turnover at each address, and calculate wt avg turnover based on total appts at each address.)
Hierachary: Region->SubRegion->Facility->Address->LocationCode
I want to know what is the difference between:
1.WtTurnover New =
VAR lowerbound=0.008
VAR upperbound=0.13
VAR rooms=CALCULATETABLE(
FILTER(
ADDCOLUMNS(
VALUES(‘Rooms’[Rooms Address])
,“T”,[Turnover New]
,“A”,[Total Appt by Address New]
)
,lowerbound<=[Turnover New]&&[Turnover New]<=upperbound
)
,ALLSELECTED('Rooms ')
,FILTERS(‘Rooms’[Rooms Region])
,FILTERS('Rooms '[Rooms Sub Region])
,FILTERS('Rooms '[Rooms Facility Name])
)
VAR numerator=SUMX(rooms,[A]*[T])
VAR denominator=SUMX(rooms,[A])
RETURN DIVIDE(numerator,denominator,BLANK())
2.WtTurnover New 2 =
VAR lowerbound=0.008
VAR upperbound=0.13
VAR rooms=CALCULATETABLE(
FILTER(
ADDCOLUMNS(
VALUES(‘Rooms’[Rooms Address])
,“T”,[Turnover New]
,“A”,[Total Appt by Address New]
)
,lowerbound<=[Turnover New]&&[Turnover New]<=upperbound
)
,ALLSELECTED(‘Rooms’)
,VALUES(‘Rooms’[Rooms Region])
,VALUES(‘Rooms’[Rooms Sub Region])
,VALUES(‘Rooms’[Rooms Facility Name])
)
VAR numerator=SUMX(rooms,[A]*[T])
VAR denominator=SUMX(rooms,[A])
RETURN DIVIDE(numerator,denominator,BLANK())
Option 1 returned what I want.
I don’t understand why at the Location Code level, option 2 returned a different results(Highlighted yellow, it returned the turnover calculated at address level, but I didnt add VALUES(Rooms Address) within option 2 and I thought allselected() would remove context from address and location code and option 2 would behave exactly as option 1.
Thanks. Any help is appreciated.
