Calculate average by group ignoring certain filter

Question

Hello,&nbsp;my data looks like the following&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Emloyee&nbsp; &nbsp; &nbsp; Group&nbsp; &nbsp; &nbsp; &nbsp; Task&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Duration2020-01-01&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22020-01-01&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42020-01-01&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;82020-01-01&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42020-01-01&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32020-01-01&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;112020-01-01&nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32020-01-01&nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32020-01-01&nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42020-01-01&nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42020-01-02&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;2020-01-02&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22020-01-02&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32020-01-02&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;52020-01-02&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22020-01-02&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;42020-01-02&nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 62020-01-02&nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42020-01-02&nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22020-01-02&nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .&nbsp;My aim is to calculate the average duaration for every employee and every task and compare them across their group.So for example Employee A completed Task aa in 2,5 Minutes ((2+4+2+2)/4 = 2,5) on average whereas the average time for the whole group for this task was 3,2 minutes ((2+4+4+2+2+5)/6 = 3,166).&nbsp; I want to do this for all tasks and all groups.I want to show only one employee ( which can be selecte via the filter pane) and the average duaration for every task he did in a certain period and the group average for every task. So I would display 2 minutes for task aa for employee A and 3,2 minutes for his group. The measure should ignore the employee filter on page level but not others like Date (I want to compare results across months) or group.&nbsp;Thanks.

mariusz · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
try this measure
Measure = 
CALCULATE(
    AVERAGE( 'Table'[Duration] ),
    ALLEXCEPT( 'Table', 'Table'[Task], 'Table'[Emloyee] ),
    ALLSELECTED( 'Table'[Emloyee] )
)
&nbsp;
Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.Please feel free to connect with me. LinkedIn 
&nbsp;

amitchandak · Answer

Anonymous ,
&nbsp;
Refer this can help. You have to use all except and filters
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

v-alq-msft · Answer

Hi,&nbsp;Anonymous&nbsp;&nbsp;Based on my research, you may create two measures as below.&nbsp;EmpAvg = 
var _emp = SELECTEDVALUE('Table'[Employee])
var _task = SELECTEDVALUE('Table'[Task])
return
CALCULATE(
    AVERAGE('Table'[Duration]),
    ALLEXCEPT('Table','Table'[Employee],'Table'[Task],'Table'[Date])
)

GroupAvg = 
var _emp = SELECTEDVALUE('Table'[Employee])
var _task = SELECTEDVALUE('Table'[Task])
return
CALCULATE(
    AVERAGE('Table'[Duration]),
    FILTER(
        ALLEXCEPT('Table','Table'[Task],'Table'[Date]),
        'Table'[Group] in 
        CALCULATETABLE(
            DISTINCT('Table'[Group]),
            FILTER(
                ALL('Table'),
                'Table'[Employee] =_emp
            )
        )
        
    )
)&nbsp;&nbsp;Result:&nbsp;Best RegardsAllan&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Hi&nbsp;v-alq-msft&nbsp;&nbsp;thanks for your answer!I tried it, however, I forgot to mention that my data is in three different tables - one fact table with the tasks and their durations and two dimension tables (one for employees and one for dates). Due to this your mesaure GroupAvg doesn't work. How can I use your measure with multiple tables? Thanks!

Forum Discussion

Calculate average by group ignoring certain filter

4 Replies

Related content

Calculating Average while ignoring zero/null values

DAX Measure for Ranking the valid rows and calculating the average rating

Calculating Weighted Average

Calculating average 12 month average

Average Calculation