cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IanWA Frequent Visitor
Frequent 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

Accepted Solutions
IanWA Frequent Visitor
Frequent Visitor

Re: Calculate Average Growth using Test sequence showing Infinity

Solved it.

 

Should have used:

 

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

 

although I don't really understand why.

2 REPLIES 2
IanWA Frequent Visitor
Frequent Visitor

Re: Calculate Average Growth using Test sequence showing Infinity

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

PwoerBIa.png

 

 

 

 

IanWA Frequent Visitor
Frequent Visitor

Re: Calculate Average Growth using Test sequence showing Infinity

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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 98 members 1,535 guests
Please welcome our newest community members: