Forum Discussion

xariet's avatar
xariet
Helper I
3 years ago
Solved

Calculating Average

Hi All, I need to do a very simple calculation, but I am stuck. Please help. I need to see in the table the countries, years, and average per country across all years. I have tried different variations of DAX but can't come up with the correct answer. For example, for Austria, I want to see 20,290.5 across all years.

 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi xariet 

    Try :

    average over years =
    CALCULATE(
        AVERAGE('table'[Sum]),
        ALLEXCEPT('table', 'table'[Country])
    )

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Assuming the Year column is from the Calendar Table, write this measure

    Avg = averagex(allexcept(Calendar,Calendar[Year]),[sum_sales])

    Ensure that there is relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.

    Hope this helps.

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi xariet 

    Try :

    average over years =
    CALCULATE(
        AVERAGE('table'[Sum]),
        ALLEXCEPT('table', 'table'[Country])
    )

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • xariet's avatar
      xariet
      Helper I
      3 years ago

      Thank you Ritaf1983 , but using your formula I still get incorrect numbers

       

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        3 years ago

        Hi xariet 

        Please attach your data table in a format that it is possible to work with ( not a picture and not a visual, data from data view) and I will check it