Forum Discussion

corange's avatar
corange
Post Patron
6 years ago

Filter on Aggregation

Hi POWER BI Team,    I need your help on a project and I will try my best to explain the desired outcome.    My company is structured into 3 Business Units (BUs) - BU A, BU B, BU C, and we have a...
Need Help
v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi corange ,

If only single or all slicers are considered to be selected, I think the below measure should work:

Measure = 
VAR _avg =
    CALCULATE (
        AVERAGEX (
            FILTER ( 'Table', 'Table'[value] >= 3 || 'Table'[BUs] <> "BU A" ),
            'Table'[value]
        ),
        ALLEXCEPT ( 'Table', 'Table'[BUs], 'Table'[state] )
    )
VAR _avgA =
    CALCULATE (
        AVERAGEX (
            FILTER ( 'Table', NOT ( 'Table'[value] < 3 && 'Table'[BUs] = "BU A" ) ),
            'Table'[value]
        ),
        ALLEXCEPT ( 'Table', 'Table'[BUs], 'Table'[state] )
    )
VAR _avgother =
    AVERAGE ( 'Table'[value] )
RETURN
    IF (
        HASONEFILTER ( 'Table'[BUs] ),
        IF (
            NOT ( ISFILTERED ( 'Table'[BUs] ) ),
            _avg,
            IF ( SELECTEDVALUE ( 'Table'[BUs] ) = "BU A", _avgA, _avgother )
        ),
        _avg
    )

 

Attached sample file: Filter on Aggreation.pbix

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

corange's avatar
corange
Post Patron
6 years ago

Hi v-yingjl , 

 

I dont understand how you get an average of 4.80 when only 4 should be considered? 

 

Where is the 0.80 coming from? 

 

Thank you. 

  • corange's avatar
    corange
    Post Patron
    6 years ago

    Hi v-yingjl , 

     

    Moreover, we can see that all of the averages are similar across all State and Employees. It doesnt look correct at all.