Can I find the Average of values in a column using a calculated column rather than a measure?

[sadsmileyface]

Would it be possible to create a caluclated column that could find the average of each salesmans sales so that looks a little like this example?

Day	Salesman	Sales	Average
Monday	John	200	277.5
Monday	Steve	350	625
Monday	Billy	100	170
Tuesday	John	355	277.5
Tuesday	Steve	900	625
Tuesday	Billy	240	170

I know I could format the a total to show the average in a visual and I have a measure that does the same thing, but I was wondering if I could do this in a calulated column? 

[ppm1 · Accepted solution]

Please try this column expression. Replace T1 with your actual table name.

AvgSales =
CALCULATE (
    AVERAGE ( T1[Sales] ),
    ALLEXCEPT ( T1, T1[Salesman] ),
    T1[Sales] > 0
)

Pat

[sadsmileyface]

Outstanding, that's cracked it! You two have been brilliant help! 

[PhilipTreacy]

Hi @sadsmileyface 

Try this

Avg = CALCULATE(AVERAGEX('DataTable','DataTable'[Sales]), FILTER('DataTable', 'DataTable'[Salesman] = EARLIER('DataTable'[Salesman])))

Regards

Phil

[sadsmileyface]

Oh that is fantastic! Would you know how I could exclude any values that were 0 and / or blanks?