Forum Discussion

TechTony's avatar
TechTony
New Member
2 years ago
Solved

visual to compare member average hours worked to team average

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, ...
Need Help
  • TechTony's avatar
    TechTony
    2 years ago

    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's avatar
TechTony
New Member
2 years ago

Thanks for the input Anonymous  and amitchandak 

 

Anonymous 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