Forum Discussion

Jan_Trummel's avatar
Jan_Trummel
Icon for Helper IV rankHelper IV
3 years ago
Solved

Average for only one year

Hello to the forum, I have the following table Sales:   Date Amount 2022-01-01 $220,00 2022-02-01 $632,00 2022-03-01 $647,00 2022-04-01 $229,00 2022-0...
Need Help
  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    Hi,

     

    you could use the same measure you have but instead of ALL use ALLEXCEPT and join only on the year column. e.g.

     

    Average Sales =

    CALCULATE(

        AVERAGE('Sales'[Amount]),

        ALLEXCEPT(Sales[Year])

    )

    if you don't have a year column: 

     

    Year = year('sales'[Date])

     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

halfglassdarkly's avatar
halfglassdarkly
Icon for Responsive Resident rankResponsive Resident
3 years ago

It might still be inferring context from Dates[Date]. 

Try ALLEXCEPT(Dates[Year]) in place of ALL(Dates[Month]).


Or try using:

 

AVERAGEX(VALUES(Dates[Year]),Sales[Amount])