Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Average of two filters in calculated column

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! 

 

BasinCodecostAverage
Stack11716
stack11516
stack21313.5
stack21413.5
williston1107.5
williston157.5
williston21115.5
williston22015.5
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

Create a new Column

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

@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
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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? 

 

NameBasinCodecostSUM filtered (per name per basin per code)Avg of "SUM filtered" per code
JustinStack1 $   17.0 $                                32.0 $                       38.0
JustinStack1 $   15.0 $                                32.0 $                       38.0
BrettStack1 $   14.0 $                                44.0 $                       38.0
BrettStack1 $   10.0 $                                44.0 $                       38.0
BrettStack1 $   20.0 $                                44.0 $                       38.0
Justin Stack2 $   13.0 $                                27.0 $                       29.0
JustinStack2 $   14.0 $                                27.0 $                       29.0
BrettStack2 $   12.0 $                                31.0 $                       29.0
BrettStack2 $   19.0 $                                31.0 $                       29.0
Justinwilliston1 $   10.0 $                                15.0 $                       20.5
Justinwilliston1 $     5.0 $                                15.0 $                       20.5
Brettwilliston1 $   11.0 $                                26.0 $                       20.5
Brettwilliston1 $   15.0 $                                26.0 $                       20.5
Justin williston2 $   14.0 $                                33.0 $                       29.0
Justinwilliston2 $   19.0 $                                33.0 $                       29.0
Brettwilliston2 $   13.0 $                                25.0 $                       29.0
Brettwilliston2 $   12.0 $                                25.0 $                       29.0

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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. 

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.