Average calculation

[harshadrokade]

I Have below data.

I want to generate a column that will give me difference between below values for each Class-

1. Average score of specific Class ( Like for CLass 1, average of Student A,B,C)
2. Average score of all the Class (Average score of all the Class will be calculated based on the Average score generated for each Class & not base on avarage of all the students) (i.e. average of all class average generated as per above 1st item)

Student data table-

Class	Student	Score
1	A	2
1	B	3
1	C	4
2	D	1
2	E	2
3	F	5
3	G	3
3	H	5
3	I	2
4	J	3
4	K	1
4	L	3

[bcdobbs · Accepted solution]

That's really odd. 

can you create a new test measure same as above but without the difference.

Test Measure

VAR AverageAllClasses = 
	CALCULATE (
		[Average by Class],
		REMOVEFILTERS()
	)
		
RETURN AverageAllClasses

  Drop that into matrix as well and send results. 

[bcdobbs]

I think you just need a measure like this:

Average by Class = 
    AVERAGEX (
        VALUES ( Result[Class] ),
        CALCULATE ( AVERAGE ( Result[Score] ) )
    )

VALUES ( Result[Class] ) creates a list of classes.
AVERAGEX then iterates over this list and calculates an AVERAGE for each.
The CALCULATE is needed as it forces a context transition so you only get an average for the current class.

You can then use the measure in a matrix visual which you can see in this demo.

[harshadrokade]

@bcdobbs Thanks sir. The card is showing 

Actually I am trying to add a column which will provide me the difference between average score by class & average of that class. I need a measure for the same.

Can you pls help me with the same which will provide me difference bewteen Average score of specific Class  & verage score by Class

[bcdobbs]

Avg Difference = 

VAR AverageAllClasses = 
	CALCULATE (
		[Average by Class],
		REMOVEFILTERS()
	)
		
RETURN [Average by Class] - AverageAllClasses

Try this.

Add a matrix visual with classes on the rows and drop this into the values.

[harshadrokade]

THanks @bcdobbs Sir.

I tried this but it is giving me wrong values as below-

The correct difference values after I manually deduct the average of class from average by class is as below.

[bcdobbs]

Can you drop average by class into the visual and also into a card and send screen shot?

(Or better send your pbix file)

[harshadrokade]

HI @bcdobbs 

This is how it looks in table visual & also in card visual. I can't send pbix file as the organisation doesn;t allow uploads.. Thanks in advance.

[bcdobbs · Accepted solution]

That's really odd. 

can you create a new test measure same as above but without the difference.

Test Measure

VAR AverageAllClasses = 
	CALCULATE (
		[Average by Class],
		REMOVEFILTERS()
	)
		
RETURN AverageAllClasses

  Drop that into matrix as well and send results. 

[harshadrokade]

@bcdobbs 

I wrongly marked this as resolved but its not.

Now I see below results

[bcdobbs]

I'm out at the moment but will fire up the demo file when I get back. Something is very odd in terms of what is being displayed. Will be a few hours.

[harshadrokade]

@bcdobbs Thanks a lot sir for rea time help. No hurry. Reply when you have a moment. Thanks again

[bcdobbs · Accepted solution]

I've updated my demo file with the code I sent and it's working.

Do you want to have a look and see if you can spot what the difference is?

Same link for demo