AverageX exclude partial year in "total"

[Dellis81]

Hello!

I am needing help in calculating annual averages excluding those years not complete (ie 2020).  The current measure I am using 

CostCenter exclOffsetsAvg =
AVERAGEX ( VALUES ( 'CALENDAR'[Year] ), [CostCenter exclOffsets0] )

Which results in this calculation,   The individual years are correct - however I would like to exclude 2020, as obvious only partial year.   The real average I want is 15,568.33.

I do want to display the 2020 value, but not include in the overall average.

And while I am asking - is there a way to change the "Total" connotation to "Average"

Thanks in advance - I always get tremendous help from this forum!  Appreciated!

[parry2k · Accepted solution]

@Dellis81 solution attached, change as per your requirement.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[amitchandak · Accepted solution]

@Dellis81 , Try

CostCenter exclOffsetsAvg =
calculate(AVERAGEX ( VALUES ( 'CALENDAR'[Year] ), [CostCenter exclOffsets0]  ),'CALENDAR'[Year]<Year(Today()))
OR
CostCenter exclOffsetsAvg =
AVERAGEX (filter( VALUES ( 'CALENDAR'[Year] ),'CALENDAR'[Year]<Year(Today())), [CostCenter exclOffsets0]  )

[Dellis81]

Thanks for your help - I was able to use your second measure, wrapped inside an if(hasonevalue) statement.

CostCenter exclOffsets = 


VAR ExclCurrentYrAvg =
    AVERAGEX (
        FILTER ( VALUES ( 'CALENDAR'[Year] ), 'CALENDAR'[Year] < YEAR ( TODAY () ) ),
        [CostCenter exclOffsets0]
    )
RETURN
    IF (
        HASONEVALUE ( 'CALENDAR'[Year] ),
        [CostCenter exclOffsets0],
        ExclCurrentYrAvg
    )

Thank you!

[Dellis81]

Thank you  - we are closer, but not quite.  Your first formula returned the same value 15,568 for all years and total line.

The second formula returned the correct value for 2017,2018,2019, and total.  The 2020 year was blank.  

My hope was I could include the 2020 value on it's unique report line, but exclude from the average.

Thank you!

[parry2k]

@Dellis81 no single clue what you need? Originally you asked avg excluding current year and now you are looking for sum and avg.

It is very hard to provide a solution when the problem is not clearly defined. We are here to help but you need to help us so that we can provide an effective solution. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please only show that table in the file where you are facing a problem.

[parry2k · Accepted solution]

@Dellis81 solution attached, change as per your requirement.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Dellis81]

Thanks for your suggestion.  Based on your input, plus another forum member, was able to piece together what I needed.   Here is my final measure.    Sum of each individual year, with the Total being the average of each year, excluding current yr.

CostCenter exclOffsets = 


VAR ExclCurrentYrAvg =
    AVERAGEX (
        FILTER ( VALUES ( 'CALENDAR'[Year] ), 'CALENDAR'[Year] < YEAR ( TODAY () ) ),
        [CostCenter exclOffsets0]
    )
RETURN
    IF (
        HASONEVALUE ( 'CALENDAR'[Year] ),
        [CostCenter exclOffsets0],
        ExclCurrentYrAvg
    )

   thank you

[Dellis81]

Thanks for your quick response - unfortunately we aren't quite there yet.   I am needing each individual year to be the SUM, however the total line needs to be the average of all complete years.

I took your measure - and tweaked.   And as you can see, the result of 3454 is a sum of 2018 and 2019

Annual Sales wAvgTotal = 
IF ( 
    HASONEFILTER ( 'Calendar'[Year] ), 
    [Sum Sales],
    CALCULATE ( AVERAGEx(values('Calendar'[Current Year]), ([Sum Sales] )), 'Calendar'[Current Year] = 0 )
)

I still think I need to be using the averagex iterator - as that works for ALL years.   The remedy is to narrow the years down excluding 2020.   But, as you can see in the second column - I am still pulling in 2020.

Annual Sales wAvgTotal =

IF (

HASONEFILTER ( 'Calendar'[Year] ),

[Sum Sales],

CALCULATE ( AVERAGEx(values('Calendar'[Current Year]), ([Sum Sales] )), 'Calendar'[Current Year] = 0 )

Below is the link to your original file with my foild attempts with yoru dataset
https://drive.google.com/file/d/106gb8DklbFyMiSiLFDYHr0mEbjaX5Kwi/view?usp=sharing