How to remove category in column with DAX-allexcept not work

[laurent_rio]

Hi,

I want to calculate group average training duration that count total per group not per position. 

I try use ALLEXCEPT Dax but not work 

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],ALLEXCEPT('Job Grade','Job Grade'[Job Grade]))

Jobgrade is my table name that have list of position , where i calculate average duration/staff use measures

Please help

[laurent_rio]

Yes it works !!! Super Thanks

[laurent_rio]

Hi it seems my DAX not work if i have filter. My filter is Business unit , 
using the formula 

Group Average Duration/Staff = CALCULATE([Duration],ALLEXCEPT(Course_Completion_Listing,Course_Completion_Listing[BU]))

will lock all the business unit to show eventhough filter applied 

Any idea ? 

[amitchandak · Accepted solution]

@laurent_rio , if you want other filters to work

Group Average Duration/Staff = CALCULATE([Duration],filter(allselected(Course_Completion_Listing),Course_Completion_Listing[BU] =max(Course_Completion_Listing[BU]) ))

[laurent_rio]

Hi @amitchandak 

Now the questions if the filter have all selection, it seems this DAX
Group Average Duration/Staff = CALCULATE([Duration],filter(allselected(Course_Completion_Listing),Course_Completion_Listing[BU] =max(Course_Completion_Listing[BU]) ))

Not give expected result

Here for example if i choose 1 BU, the result is OK 

But if i choose all BU or multiple BU Selection, i expect the group will show same total average hour/staff 

Can you help to modify the DAX so it work for all or multiple BU Selection ?

[amitchandak]

@laurent_rio , Allexpect will allow that filter to pass, means job Grade filter iwll pass

Try like

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],ALL('Job Grade'[Job Grade]))

or

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],ALLselected ('Job Grade'[Job Grade]))

Group Average Duration/Staff = CALCULATE([Average Duration/Staff],removefilters('Job Grade'[Job Grade]))