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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
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 

 

 Excel_vs_PowerBI.png

 

 

 

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
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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])))

11.png

 

If you have any other issue, please feel free to ask.


Best Regards,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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])))

11.png

 

If you have any other issue, please feel free to ask.


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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.