Average of two filters in calculated column

[Anonymous]

Hi, I'd like to calculate an average based on two filters. I know how to do this with a measure, but I'd like to do it in a calculated column. 

I'd like the calculated column to calculate the average "cost" filtered by the first and second column (as you can see, there are 2 Basins and 2 codes in my example. Below is an example table with the desired output in the last column: "Average". 

I've tried the earlier function but am having difficulty putting both the basin and code filter in, I only know how to put one of them in. 

Thanks in advance! 

Basin	Code	cost	Average
Stack	1	17	16
stack	1	15	16
stack	2	13	13.5
stack	2	14	13.5
williston	1	10	7.5
williston	1	5	7.5
williston	2	11	15.5
williston	2	20	15.5

[amitchandak]

Create a new Column

Averagre Cost = avergageX(filter(Table,table[Basin] =earlier(Table[Basin]) && table[Code] =earlier(Table[Code])),Table[cost])

[Anonymous]

@amitchandak 

Thank you for the continued support - However, the column you provided calculates the average of the entire column. I'm just looking for an average of a single sum per code per basin. If there was only one cost per code per basin per name, your method would work, but it's not correct because your method sums multiples of the same value. 

Does this make sense? 

Thanks again. 

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(AVERAGE(Data[cost]),FILTER(Data,Data[Basin]=EARLIER(Data[Basin])&&Data[Code]=EARLIER(Data[Code])))

Hope this helps.

[Anonymous]

@Ashish_Mathur @amitchandak 

Thank you both for replying to my question, that worked very well. Would you mind helping me with one more step in my project? It's more complicated. I still would like this in a calculated column not a measure. 

I've included a table below, the last column on the right is what I'm trying to accomplish. I'm trying to find the avg of column "SUM filtered" but ony using a single value for each filtered section per code. I'm trying to calculate the average summed cost (per "name") per basin, per code. 

Perhaps I don't need the "SUM filtered" column? 

Name	Basin	Code	cost	SUM filtered (per name per basin per code)	Avg of "SUM filtered" per code
Justin	Stack	1	$   17.0	$                                32.0	$                       38.0
Justin	Stack	1	$   15.0	$                                32.0	$                       38.0
Brett	Stack	1	$   14.0	$                                44.0	$                       38.0
Brett	Stack	1	$   10.0	$                                44.0	$                       38.0
Brett	Stack	1	$   20.0	$                                44.0	$                       38.0
Justin	Stack	2	$   13.0	$                                27.0	$                       29.0
Justin	Stack	2	$   14.0	$                                27.0	$                       29.0
Brett	Stack	2	$   12.0	$                                31.0	$                       29.0
Brett	Stack	2	$   19.0	$                                31.0	$                       29.0
Justin	williston	1	$   10.0	$                                15.0	$                       20.5
Justin	williston	1	$     5.0	$                                15.0	$                       20.5
Brett	williston	1	$   11.0	$                                26.0	$                       20.5
Brett	williston	1	$   15.0	$                                26.0	$                       20.5
Justin	williston	2	$   14.0	$                                33.0	$                       29.0
Justin	williston	2	$   19.0	$                                33.0	$                       29.0
Brett	williston	2	$   13.0	$                                25.0	$                       29.0
Brett	williston	2	$   12.0	$                                25.0	$                       29.0

[Ashish_Mathur]

Hi,

You are welcome.  I cannot appreciate why you would want to compute the average as a calculated column (when infact it can be solved with a measure quite easily) and therefore i would not be contributing to this one.  I hope someone else can help you.

[Anonymous]

@Ashish_Mathur 

I'm sorry, I should have explained this in the beginning, I'm placing this data on a vertical bar chart. I want the magnitude of the bar to represent the amount that each "Name" is greater than or less than the average (which as you know, the average is summed, then filtered by Basin and code. When I try this with a measure and split the bar chart by "Name", the measure can only see the values within each "Name" so it's no longer an average of each "code" by each "basin". I'm not sure how to get around this. 

[Ashish_Mathur]

Hi,

I still think this can be resolved with a measure.  I cannot visualise your visual though so i still cannot help.  Please also note that even if one writes a calculated column formula to get your desired result, then pitfall will be that the calculated column formula will not respond to slicer selections whereas the measure will.