cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

 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
1 ACCEPTED SOLUTION
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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
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

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.

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

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
Anonymous
Not applicable

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.

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors