cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors