Filter on Aggregation

[corange]

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 report showing results across the company at a state level and BUs Level. 

One of the metric I need is the daily average job number per employee group per state and BU. In my dataset, the number of jobs are sum by employee and date.  I simply used the column where the number of jobs are counted and used the function AVERAGE when the column is droppped in FIELD under VALUE. 

The results are displayed using a MATRIX and at the top, I have got a SLICER that allows the user to see results for the entire company or select a specific BU. 

Now, where I am stuck is that for BU A ONLY, if an employee has completed less than 3 jobs a day, then they should be excluded from the average for that day. I am don't know at all how to go about this.

I hope I am making sense and that someone can assist? 

Thank you. 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-yingjl]

Hi @corange ,

Try to use this measure:

average = 
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[value] ),
        ALLEXCEPT ( 'Table', 'Table'[BUs], 'Table'[state] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[BUs] ) = "BU A",
        CALCULATE (
            AVERAGEX (
                FILTER ( 'Table' , NOT ( 'Table'[value] < 3 && 'Table'[BUs] = "BU A" ) ),
                'Table'[value]
            ),
            ALLEXCEPT ( 'Table', 'Table'[BUs], 'Table'[state] )
        ),
        _avg
    )

When the slicer is BU A, it will filter the values that < 3 to calculate average based on state and BU

Here is my sample table and result:

Attached my sample file that hopes to help you: Filter on Aggeration.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]

Hi @Yingjie Li,

Thank you. Could you explicitly write the aggregation using your data and see how we obtain the following results when nothing is selected on the slicer? 

The less than 3 jobs rule for BU A would still need to apply in the overall overview too when no filter from the slicer is selected and we want to see the results for the entire organisation. Is this automatically done using your measure? 

Thank you so much for your help. 

[v-yingjl]

Hi @corange ,

Try to modify the measure like this to show the correct inital average if there is no BU selected on the slicer:

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

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]

Hi, 

Thanks for your answer but I dont get it. 

Based on the sample you have sent through, the desired outcome would be to not include anything below or equal to 3 in any calculations and still be able to see results with the same rule when selecting a specific BU. 

For example, using the last measure you have sent through: 

The desired outcome would be to have the calculation not including the first row where the value is two. This belongs to BU A as well and the average is 3 whihc mean that it is being included in my average. 

I want to be able to apply the rule and view the result overall for the company or by selecting a BU. 

[v-yingjl]

Hi @corange ,

Please let me know whether you want to filter all datas which values>=3 before calculate average.

If so, just create measure like this:

Measure = 
VAR tab =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[value] >= 3 ),
        'Table'[BUs],
        'Table'[date],
        'Table'[state],
        'Table'[employee],
        'Table'[value]
    )
RETURN
    AVERAGEX ( tab, [value] )

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]

Hi v-yingjl, 

Thank you. No I want to filter <= 3 only when it is against BU A. 

[v-yingjl]

Hi @corange ,

If you want to filter <=3 only choose BU A and calculate average noramlly if no BU has been selected, I think my previous posted measure [average] has achieved it.

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]

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. 

[v-yingjl]

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]

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]

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]

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]

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. 

[corange]

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. 

[amitchandak]

@corange , You have to try something like this

AverageX(filter(summarize(Table, Table[employee], "_sum" ,sum(Table[No of jobs])),[_sum]>3),[_sum])

or

AverageX(filter(summarize(Table, Table[employee], "_sum" ,sum(Table[No of jobs])),[_sum]>3),divide([_sum],count([employee])))