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

 

Need Help

16 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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])))

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