Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Havinng Clause implement in DAX

select AVG (Salary) ,title from Employee Group By title having AVG(Salary)>25000   this query i would like to implement in DAX measure. not on drag n drop with Average function
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Anonymous,

     

    Please refer to below formula to create a calculated table:

    Filter Table =
FILTER (
    SUMMARIZE (
        Employee,
        Employee[Title],
        "Average salary", AVERAGE ( Employee[Salary] )
    ),
    [Average salary] > 25000
)

    Regards,
    Yuliana Gu

alanhodgson's avatar
alanhodgson
Solution Supplier
9 years ago

Hey Anonymous,

 

You can create a measure using the following format:

 

Average Salary = CALCULATE(AVERAGE([Column]), FILTER(ALL('Table'[Column]), [Column] > 25000)

 

Hope this helps,

 

Alan

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    this is not working,

    it is bringing all the Title even though there is no match.

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous,

       

      Please refer to below formula to create a calculated table:

      Filter Table =
FILTER (
    SUMMARIZE (
        Employee,
        Employee[Title],
        "Average salary", AVERAGE ( Employee[Salary] )
    ),
    [Average salary] > 25000
)

      Regards,
      Yuliana Gu

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Thanks Yulugu,

        so can't we write a measure for this instead of calculated table.