Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Using Whatif Measures and Average on Measures

I have a dataset as below.

 

ParentChild1MetricsVal WhatIf Parameter
P1C1M10.7 C1val1
P1C1M20.8 C2val2
P1C2M30.9 C3val3
P1C2M40.6 C4val4
P2C3M50.8   
P2C3M60.8   
P2C4M70.9   
P2C4M80.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

  AvergeWeightAvg
P1C1Avg(M1, M2)-->Avg(M1, M2)*val1
P1C2Avg(M3, M4)-->Avg(M3, M4)*val2
P2C3Avg(M5, M6)-->Avg(M5, M6)*val3
P2C4Avg(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
johnt75
Super User
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
    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] )

 

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

lbendlin
Super User
Super User

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors