Forum Discussion

IanWA's avatar
IanWA
Regular Visitor
9 years ago
Solved

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
 

 

  • Solved it.

     

    Should have used:

     

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

     

    although I don't really understand why.

2 Replies

  • IanWA's avatar
    IanWA
    Regular Visitor

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

     

     

     

     

    • IanWA's avatar
      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.