Calculating Average with Prorated Values

[dvillegas]

I am trying to calculate the average score per month for a group of people. The calc should be sum of score divided by sum of months. The  problem I am having is when a person has more than one score for a month because they changed groups in the middle of a month. In these cases, those individuals scores are over-represented because their score is being counted for each group. How can I sum the scores to only count one value per month?

The data looks like the following:

As you can see Member 2 left Group B in the middle of March and entered Group C. As such, Member 2 has two entries for March but should only have one score for the month. Because of this, they will be counted twice in a normal average calculation.

The result I am expecting is 5.5 which is the sum of the score for each month (33) divided by the sum of monthly portions (6).

The result I am getting is 6.14 which is the sum of all the scores (43) divided by the sum of monthly portions (6).

Any help would be greatly appreciated.

[dax · Accepted solution]

Hi dvillegas, 

You could use below measure to get 5.5

Measure 3 = SUMX(ALL(TEST),TEST[Score]*TEST[Monthly Portion])/SUMX(ALL(TEST),TEST[Monthly Portion])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@dvillegas try following measures

Scores = SUMX( SUMMARIZECOLUMNS('Table'[MemberId], 'Table'[Month] ), CALCULATE( MAX( 'Table'[Score] ) ) )

Members = COUNTX( SUMMARIZECOLUMNS('Table'[MemberId], 'Table'[Month] ), CALCULATE( COUNTROWS( 'Table' ) ) )

Score / Member = DIVIDE( [Scores], [Members] )

[dvillegas]

When I put these measures in a visualization, I receive the following error:

Is this because I have