cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Average based on criteria from another column

Hi,

I have some information in CSV and wants to do calculations based on criteria (an average of output if ID's are the same)

In Excel à Column A contains "output" column B and C are ID's and in column D is the Excel calculation.

`=AVERAGEIF(C:C;B2;A:A)`

is the Excel formula in D2

In Power Bi I've tried EARLIER and VLOOKUPS but I don't get the desired result. I think it isn't very difficult, but I cannot find it 😞

Thnx,

Manfred

1 ACCEPTED SOLUTION
Microsoft

Hi @Anonymous,

In your resource data, the ID1 and ID2 are same, so you can add any one to the filter. Please create a calculated column using the following formula and get expected result.

AVG = CALCULATE(AVERAGE(Table5[Output]),FILTER(Table5,Table5[ID1]=EARLIER(Table5[ID1])))

Best Regards,
Angelia

2 REPLIES 2
Microsoft

Hi @Anonymous,

In your resource data, the ID1 and ID2 are same, so you can add any one to the filter. Please create a calculated column using the following formula and get expected result.

AVG = CALCULATE(AVERAGE(Table5[Output]),FILTER(Table5,Table5[ID1]=EARLIER(Table5[ID1])))

Best Regards,
Angelia

Anonymous
Not applicable

Hi Angelia,

Thnx for helping me. This works great and now I know what went wrong.

For the explanation I've created an example in Excel named the column Output. When I import the Excel file it goes fine. Normally I'll do first a SUMX to calculate different columns and then I want to do the earlierformule. SUMX only works with a measure

Your answer works perfect with my question so we close this post and I'll create a new explanation

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors