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 ,

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's avatar
    corange
    Post Patron
    6 years ago

    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's avatar
      v-yingjl
      Community Support
      6 years ago

      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's avatar
        corange
        Post Patron
        6 years ago

        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.