Calculate Average based on several filters from different columns

[Anonymous]

I have this table that I need to get the average of Days, from 1 department AND 3 roles.

For example, I need to get the Days Average of P&E between SVP, VP and Exec.Director only.

I tried using the CALCULATE to create a measure, but I just can't seem to put in the correct filters or expression in the measure. Appreciate the help. Here is a sample of the data.

I will use this like a standard bar for average, that would not change regardless of the filters chosen in a page

[v-jingzhang]

Hi @Anonymous 

You can try this measure

Average Value =
CALCULATE (
    AVERAGE ( 'TableName'[Days] ),
    FILTER (
        ALL ( 'TableName' ),
        'TableName'[Department] = "P&E"
            && 'TableName'[Role] IN { "SVP", "VP", "Exec.Director" }
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[PC2790]

You can try something like:

CALCULATE(AVERAGE([DAYS]),FILTER(<TableName>,<logic based on which filter is to be applied>))

[Anonymous]

Should I use the AND() logic on different tables? Sorry I tried using this awhile ago and the filter is not getting the values

[PC2790]

CALCULATE DAX allows you to have multiple Filters that too you can have on different tables. something like:

..............,FILTER(Orders,Orders[Amount]=1),FILTER(Suppliers,Suppliers[Country]= "EUROPE"))

[Anonymous]

Sorry, I meant different columns.

CALCULATE(AVERAGE([DAYS]), FILTER(Table1, Table1[Department]="P&E"), FILTER(Table1, Table1[Role]="SVP"), FILTER(Table1, Table1[Role]="VP"), FILTER(Table1, Table1[Role]="Exec.Director")

This one is not working. I tried it, and it's not showing any data (the dark blue one)

[PC2790]

This should work:

CALCULATE(AVERAGE(Table1[DAYS]),FILTER(Table1,Table1[Department] in {"P&E","SVP","VP","Exec.Director"}))