Calculate Average except one column

[Anonymous]

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 Rate]),ALL(Query1[State]))

But this is not giving me the expected result as you can see in the below image. Im expecting 55.96 for all the rows in the Table visual. But its not giving me that. Can anyone explain me how can i get it?

[Sujit_Thakur]

Dear friend I didn't understood the question well, But to the point which I understood you may try this Avg National = CALCULATE(AVERAGE(Query1[Bill Rate])) Please explain it once more so that I could help

[Anonymous]

@Sujit_Thakur 

Hi Sujit,

Im trying to get the National Avg.  Basically i dont want column State to play any role in the calculation. In the below image there are two table visuals. In the 2nd table, i have division, assignment type, speciality and state. I want the average to be calculated including division, assignment type, speciality but excluding state which should be the national avg.

[amitjzaveri · Accepted solution]

@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]))

[Anonymous]

@amitjzaveri  Superb 

Thanks a lot.. 

[Anonymous]

Please can anyone help me with this please...

[amitchandak]

@Anonymous , Try like

Avg National = CALCULATE(AVERAGE(Query1[Bill Rate]),removefilters(Query1[State]))

or best is create a table of distinct State join with this table and try like
Avg National = CALCULATE(AVERAGE(Query1[Bill Rate]),ALL(State[State]))

[Anonymous]

@amitchandak 

Even Removefilters is not giving me the expected results. In below image, i expect 92.14 in all the 3 rows. Please please help me.

I cant have a different state table as the current state filter is already applied for other visuals.

[amitchandak]

@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]

@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.