Calculate average considering all columns except one

[Anonymous]

Hi Team,

I have a Table matrix with 5 columns added in it and "State" is one of the column.  

I want to get the average sales at the national level, i.e, that measure should ignore filter from "State".

Is the below DAX correct?

Avg National = CALCULATE(AVERAGE(Query1[Sale]),ALL(Query1[State]))

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try to use allexcept and add 'country' and 'Speciality' to the filter condition.

Measure = CALCULATE(AVERAGE('Table'[sale]),ALLEXCEPT('Table','Table'[Speciality],'Table'[country]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , try like

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

or

Avg National = CALCULATE(AVERAGE(Query1[Sale]),all(Query1))

or

Avg National = CALCULATE(AVERAGE(Query1[Sale]),allexcept(Query1,Query1[Country]))

[Greg_Deckler]

@Anonymous Try ALLEXCEPT to remove filters on everything except particular columns or REMOVEFILTERS. Your code is correct if you are trying to ignore filters on State column

[Anonymous]

@Greg_Deckler Hi Greg,

Below is how my table looks like. In the Average - National level, I except only 2 values, one for Rap and one for Classic. But currently its not giving me the expected result.

how can i modify my DAX to get the correct value?

Division	Assignment Type	Speciality	State	Average -State level	Average- National level
North	Trans	Rap	California	$83.09	$83.09
North	Trans	Rap	Florida	$68.60	$68.60
North	Trans	Rap	Illinois	$82.80	$82.80
North	Trans	Rap	Missouri	$65.00	$65.00
North	Trans	Classic	California	$87.20	$87.20
North	Trans	Classic	Florida	$75.46	$75.46
North	Trans	Classic	Missouri	$88.00	$88.00

[Greg_Deckler]

@Anonymous What is your expected result?

[Anonymous]

@Greg_Deckler For Rap, i should be getting 77.28 in all 4 & for Classic it should be 80.06.

Basically, i need a national level average.