DAX/PowerBI - Average Group Value

2
votes

I have a table in PowerBI similar to the following:

Table1 
Name     Group     GroupScore
Jim      1         75
Al       1         75
Becky    1         75
Ann      2         10
Cody     2         10
Zack     3         90
Jane     4         90

I need a Measure that will tell me the Average GroupScore.

In other words, if all Groups are selected, I want the result to be (75+10+90+90)/4 = 66.25

A simple AVERAGE([GroupScore]) yields an incorrect result because it doesn't take into account the fact that there are only 4 groups. The GroupScore is actually a total of all the Group Members scores added up. A simple average will give me (75 + 75 + 75 + 10 + 10 + 90 + 90) / 7 = 60.7 which means that Groups 1, 2, and 3 scores are "double counted" and have an unfair weight.

1
powerbidax

1 Answers

6
votes

The following DAX measure can do:

Average = 
AVERAGEX(
    DISTINCT(SELECTCOLUMNS(Table1, "Group", Table1[Group], "GroupScore", Table1[GroupScore])), 
    [GroupScore]
)

So basically it gets the distinct column values for Group and GroupScore and takes the average.

result

It also works with Group filter.

result2