topic Re: Using Whatif Measures and Average on Measures in DAX Commands and Tips

Using Whatif Measures and Average on Measures

Anonymous — Wed, 23 Feb 2022 00:43:01 GMT

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	C4	val4
P2	C3	M5	0.8
P2	C3	M6	0.8
P2	C4	M7	0.9
P2	C4	M8	0.9

I have to calculate averages at different parent level.

1) Looking to see how to calculate a Weight Measure average at Child1 .I was able to do this is a Summarize table

		Averge	WeightAvg
P1	C1	Avg(M1, M2)	-->Avg(M1, M2)*val1
P1	C2	Avg(M3, M4)	-->Avg(M3, M4)*val2
P2	C3	Avg(M5, M6)	-->Avg(M5, M6)*val3
P2	C4	Avg(M7, M8)	-->Avg(M7, M8)*val4

2) Looking to see how to calculate a Weight Measure average at P

	Average(of Averages)	Average(of Weighted Average)
P1	-->AVG( Avg(M1, M2) , Avg(M3, M4))	-->avg(Avg(M1, M2)val1, Avg(M3, M4)val2)
P2	-->AVG( Avg(M5, M6) , Avg(M7, M8))	-->avg(Avg(M5, M6)val1, Avg(M7, M8)val2)

I am having issues at this level because of Averge of a measure whose values are changing. I have a date filed also in the parent tables

Please suggest some ways to handle this scenarios.What is the easiest way to handle this. Creating multiple summarize table is causing Filtering issues also because of unidirectional filter( single)

Re: Using Whatif Measures and Average on Measures

lbendlin — Wed, 23 Feb 2022 17:49:24 GMT

What role does the "Val" column play in this scenario? What's the range of the WhatIf? parameters?

Re: Using Whatif Measures and Average on Measures

Anonymous — Thu, 24 Feb 2022 01:56:29 GMT

Column "Val" - It is the value corresponding to the Metrics. This value should be rolled up to valculate the average values for Child ( All Metrics for the child). All the child average will go parent ( group by Rollup Concept)

To the Child averages we want to apply the WhatIF and it should get reflected on Parent.

What's the range of the WhatIf? parameters? Yes Parameter.. % range ( 1 to 100%)

Re: Using Whatif Measures and Average on Measures

johnt75 — Thu, 24 Feb 2022 09:56:34 GMT

You 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] )