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

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

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

      Hi 

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        6 years ago

        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.