Filter on Aggregation

Hi v-yingjl , 

Just to clarify things, I want the <= 3 to be removed from all calculations no matter if I select BU A or not. In the summary table, if all BUs are selected then it will still remove <= 3 for BU A and present the results for all and when selecting BU A, then it will also remove <= 3 and present the result. 

So whihc measure should I go with? 

where I am confused it is the results you are getting for your average. It doesnt match what I would expect. What can we do to clarify the situation and make sure? 

Thank you. 

The <=3 Job for BU A applies no matter if I select the BU A to view averages or if I want to see averages across all BU. When an employee does less than 3 jobs a day in BU A, it is automatically removed from the calculation. 

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.

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. 

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.

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. 

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.