visual to compare member average hours worked to team average

[TechTony]

Hi,

I am hoping someone in the community can provide some guidance as I don't seem to be able to figure out where I am going wrong.

I have a report that has slicers for MemberID, CompanyName, Date and Team

I am using a clustered bar chart to compare Member average time logged per ticket type against the whole teams average for the same type (there are three different teams with different Members in each team).

There are filters on the page and across all pages which I need to keep as the datasets have multiple deparments and multiple ticketing boards so there are filters to limit the data to a single department and specific ticketing baords for this specific report.

To calculate the member average i have a measure called "Average Hours" which averages the total hours per ticket.

To calculate the team average I have a measure with a DAX query detailed below, I have tried many variations with changing which columns are in the ALLEXCEPT, using REMOVEFILTER instead and other forum and chatgpt suggested alternatives.

What I am expecting is when i use the memberID or CompanyName slicer the team average should not change as I want to compare that persons average either across all companies or for a specific company against the whole teams average for all members and companies but when I use the slicers the team average changes each time.

I would also expect that when no member or company is selected in the slicer the member and team average should be the same as the visual would be showing the whole team average if no member or company is filtered.

Hoping someone can point me in the right direction

T1 Average Hours =

CALCULATE(

    [Average Hours],

    FILTER(

        ALLEXCEPT('Time_Entry_data', 'Time_Entry_data'[Type], Time_Entry_data[date_start_UTC],

        'Time_Entry_data'[work_role] = "Service Desk"

    )

)

below are two screenshots one with no filters and the other with one filtered to one company (redacted the company name), as you can see the T1 average hours changes when filtered by company name which in this case I would expect it to show the whole teams average for that company compared to the the whole teams average for all companies

[TechTony · Accepted solution]

Managed to figure out the issue

For some reason it didn't like one of my page filters even if I added the page filter to the ALLEXCEPT() and had left the relationship between the table and my date table as cross filter when I was trying different things.

I ended up changing to REMOVEFILTER() and entered the company_name and MemberID and removed the problematic page filter and everything seems to be working.

I just created a seperate table with all the distinct values of problamtic columns I was wanting to filter and set a relationship which allowed me to retain the required filtering, not ideal but a work around.

Thanks to everyone who gave input

[TechTony]

Thanks for the input @v-yohua-msft  and @amitchandak 

@v-yohua-msft I tried the same thing replicating the table you made which with that small dataset it did work and was showing correct but when I added a different Work_Role into the table it no longer worked.

@amitchandak I tried your two DAX queries but unfortunately still get the same result, I had to alter your first recommednation a bit so PowerBi was happy with it but the result still showed the issue persisting.

I thought to simplify the issue and to only work on the Team average and not try and test by splitting by type and just work with the team average across all types. I also removed the date slicer to remove date filters from the issue.

I have put the Team average measure into a card which shows the calcualted average with the expectation that if I use the member or company name slicer the calculated value should not be changed but the measure even though I have tried using ALL and REMOVEFILTERS in the measure it is still applying the member and company name slicers to the Calculate.

This is the DAX query for the team average which I would thing would simply remove the memberID and company filters and average for the relevant team

Test T1 Average Hours =

CALCULATE(

    AVERAGE(Time_Entry_data[Hours - Actual]),

    REMOVEFILTERS(Time_Entry_data[company_name], Time_Entry_data[member_id]),

    Time_Entry_data[work_role] = "Service Desk"

)

I am sure my issue is around the DAX query not ignoring/removing the slicer filters but I cannot figure out why it won't remove those filters

[v-yohua-msft]

Hi, @TechTony 

I create a sample table:

Then create measures and try the following DAX expression:

Average Hours = AVERAGE('Table'[Hours])

T1 Average Hours = 
CALCULATE(
    [Average Hours],
    REMOVEFILTERS('Table'[MemberID], 'Table'[CompanyName]),
    FILTER(
        ALLEXCEPT('Table', 'Table'[Type], 'Table'[date_start_UTC]),
        'Table'[work_role] = "Service Desk"
    )
)

Put fields in the bar chart, and create slicers:

When I select the memberID or CompanyName, here is my preview:

You can see that there is no change in T1 Average Hours after changing the company.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@TechTony , Try like

CALCULATE(
AverageX(Values( 'Time_Entry_data'[Team]) [Average Hours]
, Removefilters( 'Time_Entry_data'[Team]) )

or

Team Average Hours =
CALCULATE(
[Average Hours],
ALL('Time_Entry_data'[MemberID], 'Time_Entry_data'[CompanyName]),
VALUES('Time_Entry_data'[Type]),
VALUES('Time_Entry_data'[Date_Start_UTC]),
'Time_Entry_data'[Work_Role] = "Service Desk"
)