Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 fro...
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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 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
    6 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
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous What is your expected result?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.