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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaedcarrell
New Member

Creating Measure - Conditional Average

Hey Everyone,

 

Need help with this for a class. I am trying to create a conditional average to tell the average enrollment over the total number of years given that a school "Type" is "Independent". 

 

So far what I've got is: Measure = if(CollegeInfo[Type] = "Independent", AVERAGE(Enrollment2[Enrollment])) however this is giving me the error : A single value for column 'Type' in table 'CollegeInfo' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Any help on this would be greatly appreciated!

1 ACCEPTED SOLUTION
dilumd
Solution Supplier
Solution Supplier

Try below,

 

Measure = CALCULATE(AVERAGE(Enrollment2[Enrollment]),FILTER(CollegeInfo,CollegeInfo[Type] = "Independent"))

View solution in original post

4 REPLIES 4
dilumd
Solution Supplier
Solution Supplier

Try below,

 

Measure = CALCULATE(AVERAGE(Enrollment2[Enrollment]),FILTER(CollegeInfo,CollegeInfo[Type] = "Independent"))

Worked like a charm. Thank you!

michaedcarrell
New Member

Hey Everyone,

 

Trying to create a measure in Power BI for a class. This measure needs to be an Average for school a "Type" which is "Independent" over several years (2011, 2012, 2013, 2014, 2015, 2016). I have quite a bit of experiance in Excel but am stuck in thinking of cell specific references instead of being able to code in columns. 

 

I have been able to come up with =if(CollegeInfo[Type] = "Independent",Avereage(Enrollement2[Enrollment]))

 

Off this though I am getting this error: A single value for column 'Type' in table 'CollegeInfo' cannot be determined. The can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Most likely it's something simple I haven't delt with yet but regardless need help finding a fix for this. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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