Forum Discussion
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
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]))
9 Replies
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]) ))
- 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
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.
