Calculating Average

[xariet]

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.

[Ritaf1983 · Accepted solution]

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

[Ritaf1983 · Accepted solution]

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 · Accepted solution]

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

[Ritaf1983]

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

[xariet]

Unfortunately I don't have an option to attach files here...

[Ritaf1983]

You can save it on one drive and attach a link :

or just pase a table :
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[Ashish_Mathur]

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.