Forum Discussion
Calculating Average with Prorated Values
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.
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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] )When I put these measures in a visualization, I receive the following error:
Is this because I have
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
