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

     

     

Sujit_Thakur's avatar
Sujit_Thakur
Solution Sage
5 years ago
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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Please can anyone help me with this please...

    • amitjzaveri's avatar
      amitjzaveri
      Resolver II
      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]))

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        amitjzaveri  Superb 

         

        Thanks a lot..