Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Average except one column

Hi all,   Below is a summary of what i have in my data.  I need to calculate National Avg in the second Table below. My query currently looks like  Avg National = CALCULATE(AVERAGE(Query1[Bill...
Need Help
  • amitjzaveri's avatar
    amitjzaveri
    5 years ago

    Anonymous  Can you try this? It worked for me in one of the scenarios

     

    Avg National = 
CALCULATE(AVERAGE(Query1[Bill Rate]),ALL(Query1[State]),ALLSELECTED(Query1[Division],Query1[Assignment Type],Query1[Speciality]))

     

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , then the way is to use all and push the filter you need

 

example

Avg National = CALCULATE(AVERAGE(Query1[Bill Rate]),filter(ALL(Table), Table[Assignment_type] in values(Table[Assignment_type]) && Table[division] in values(Table[division]) ))
or
Avg National = CALCULATE(AVERAGE(Query1[Bill Rate]),filter(ALL(Table), Table[Assignment_type] in allselected(Table[Assignment_type]) && Table[division] in allselected(Table[division]) ))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak  Unfortunately, these DAX are also not giving me the expected result. Kindly review the below and let me know if im missing anything here.