Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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