cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not 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 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)

3 REPLIES 3
Super User

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    resultParent 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    resultAvg = IF( ISINSCOPE( ParentChild[Child1] ), [Child Avg], ParentChild[Parent Avg] )Weighted Avg = IF( ISINSCOPE( ParentChild[Child1] ), [Child Weighted Avg], ParentChild[Parent Weighted Avg] )`

Anonymous
Not applicable

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%)

Super User

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors