Forum Discussion
Anonymous
4 years agoNot applicable
Using Whatif Measures and Average on Measures
I have a dataset as below. Parent Child1 Metrics Val WhatIf Parameter P1 C1 M1 0.7 C1 val1 P1 C1 M2 0.8 C2 val2 P1 C2 M3 0.9 C3 val3 P1 C2 M4 0.6 ...
johnt75
Super User
4 years agoYou can create separate measures for child average and parent average, with both standard and weighted versions, then combine them into an overall version which you can use in your reports and which will use the correct measure depending on what level of the hierarchy it is at.
Child Avg =
IF(
ISINSCOPE( ParentChild[Child1] ),
VAR currentChild =
SELECTEDVALUE( ParentChild[Child1] )
RETURN
CALCULATE(
AVERAGE( ParentChild[Val] ),
REMOVEFILTERS( ParentChild ),
TREATAS( { currentChild }, 'ParentChild'[Child1] )
)
)
Child Weighted Avg =
IF(
ISINSCOPE( ParentChild[Child1] ),
VAR currentChild =
SELECTEDVALUE( ParentChild[Child1] )
VAR weighting =
LOOKUPVALUE( WhatIf[Value], WhatIf[Key], currentChild )
RETURN
[Child Avg] * weighting
)
Parent Avg =
VAR currentParent =
SELECTEDVALUE( ParentChild[Parent] )
VAR result =
AVERAGEX(
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE( ParentChild, ParentChild[Parent], ParentChild[Child1] ),
"@avg", [Child Avg]
),
TREATAS( { currentParent }, ParentChild[Parent] )
),
[@avg]
)
RETURN
result
Parent Weighted Avg =
VAR currentParent =
SELECTEDVALUE( ParentChild[Parent] )
VAR result =
AVERAGEX(
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE( ParentChild, ParentChild[Parent], ParentChild[Child1] ),
"@avg", [Child Weighted Avg]
),
TREATAS( { currentParent }, ParentChild[Parent] )
),
[@avg]
)
RETURN
result
Avg = IF( ISINSCOPE( ParentChild[Child1] ), [Child Avg], ParentChild[Parent Avg] )
Weighted Avg = IF( ISINSCOPE( ParentChild[Child1] ), [Child Weighted Avg], ParentChild[Parent Weighted Avg] )