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 ,

Based on your description, I want to confirm something based on my sample:

  1. No matter which BU has been selected, all of values <=3 should be ignored to calcualte average
  2. If all BUs are selected, just ignore values <=3 in BU A ,other BU values <=3 should be retained to calcualte average
  3. When select BU A, all of values <=3 should be ignored to calcualte average
  4. If no BU is selected, all of values <=3 should be ignored to calcualte average

If my understanding is still partly wrong, please let me know to better help you.

 

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 , 

 

Please see below 

 

<< Based on your description, I want to confirm something based on my sample:

  1. No matter which BU has been selected, all of values <=3 should be ignored to calcualte average - NO, value <= 3 should be removed from calculation only for BU A. 
  2. If all BUs are selected, just ignore values <=3 in BU A ,other BU values <=3 should be retained to calcualte average - YES - Average should include all values except <= 3 for BU A. 
  3. When select BU A, all of values <=3 should be ignored to calcualte average - YES 
  4. If no BU is selected, all of values <=3 should be ignored to calcualte average - NO, all values should be included in the average but only <=3 value should be excluded for BU A.

I hope this helps. 

 

Thank you. 

  • 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.