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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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])
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.