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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
IanWA
Regular Visitor

Calculate Average Growth using Test sequence showing Infinity

I need to display the average growth for students from one test to the next where each test has scores for a number of areas.  I can't use dates as I'm after growth between from test 1 to up to to 6 other tests.

 

I'm using;  Growth1 = AVERAGEX(Sheet1,  ([Ass2] - [Ass1]) / [Ass1]) as measures and

Ass1 = AVERAGEX(FILTER(Sheet1, Sheet1[TestOrder] = 1), [Score]) as measures.  The data is like this:

Student	Subject	Area	Score	TestOrder
A	Math	Count	50	1
A	Math	Count	60	2
A	Math	Count	70	3
A	Math	Magnitude	55	1
A	Math	Magnitude	65	2
A	Math	Magnitude	75	3
A	Math	Patterns	45	1
A	Math	Patterns	55	2
A	Math	Patterns	65	3
B	Math	Count	60	1
B	Math	Count	70	2
B	Math	Count	80	3
B	Math	Magnitude	65	1
B	Math	Magnitude	75	2
B	Math	Magnitude	85	3
B	Math	Patterns	90	1
B	Math	Patterns	85	2
B	Math	Patterns	90	3
C	Math	Count	30	1
C	Math	Count	50	2
C	Math	Count	60	3
C	Math	Magnitude	45	1
C	Math	Magnitude	45	2
C	Math	Magnitude	55	3
C	Math	Patterns	65	1
C	Math	Patterns	70	2
C	Math	Patterns	65	3
 

 

1 ACCEPTED SOLUTION
IanWA
Regular Visitor

Solved it.

 

Should have used:

 

Growth1 = (AVERAGEX(Sheet1,  [Ass2]) - AVERAGEX(Sheet1,[Ass1])) / AVERAGEX(Sheet1, [Ass1])

 

although I don't really understand why.

View solution in original post

2 REPLIES 2
IanWA
Regular Visitor

Sorry, left out the screen shot of the issue.  I'm getting infinity or -1.

PwoerBIa.png

 

 

 

 

IanWA
Regular Visitor

Solved it.

 

Should have used:

 

Growth1 = (AVERAGEX(Sheet1,  [Ass2]) - AVERAGEX(Sheet1,[Ass1])) / AVERAGEX(Sheet1, [Ass1])

 

although I don't really understand why.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.