Forum Discussion
Calculate Average except one column
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 , 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]))
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.
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]) ))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.