Average of unique column

[jinweitan]

Hi all,

I have a set of data and would like to calculate the average of the unqiue

The raw data set is as below:

GUID	Type	Score
1	a	90
1	a	90
1	a	null
1	b	100
2	a	100
2	a	null
2	b	95
3	a	50
3	a	50
3	a	50

I would like to make it Becoming these:

GUID	Type	Score
1	a	90
2	a	100
3	a	50

Hence Average for Type a is (90+100+50)/3 = 46.67

and for Type b

GUID	Type	Score
1	b	100
2	b	95

The average is (100+95)/2 = 97.5

I've tried a few measures but it seems to take account for the duplication including

Measure = AVERAGEX

(SUMMARIZE ('Table 1'[GUID]), CALCULATE(AVERAGE('Table 1'[Score])))

Thanks a lot

[Anonymous · Accepted solution]

Hi @jinweitan ,

Based on my test, it is suggested to create a calculated table.

Table 2 =
DISTINCT (
    SELECTCOLUMNS (
        'Table',
        "GUID", [GUID],
        "Type", [Type],
        "Score",
            CALCULATE (
                MAX ( 'Table'[Score] ),
                ALLEXCEPT ( 'Table', 'Table'[GUID], 'Table'[Type] )
            )
    )
)

Create a measure.

MaxScore =
IF (
    HASONEVALUE ( 'Table 2'[GUID] ),
    SUM ( 'Table 2'[Score] ),
    AVERAGE ( 'Table 2'[Score] )
)

The result is this. Change the name of Total to Average.

You can check more details from here.

Best Regards,

Stephen Tao

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

[amitchandak]

@jinweitan , Try like

Measure = AVERAGEX(SUMMARIZE ('Table 1'[GUID],'Table 1'[Score]),[score])