Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate average considering all columns except one

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]))
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

      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?

      DivisionAssignment TypeSpecialityStateAverage -State levelAverage- National level
      NorthTransRapCalifornia$83.09$83.09
      NorthTransRapFlorida$68.60$68.60
      NorthTransRapIllinois$82.80$82.80
      NorthTransRapMissouri$65.00$65.00
      NorthTransClassicCalifornia$87.20$87.20
      NorthTransClassicFlorida$75.46$75.46
      NorthTransClassicMissouri$88.00$88.00
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.