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

      • corange's avatar
        corange
        Post Patron
        6 years ago

        Hi  , 

         

        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.