disregard duplicated values in calculation

[Anonymous]

Hi I have a table with categories with duplicated values and I wanted to have a average, but disregarding the duplicates, does someone know a way to calculate it?

The table is like this below, and I have a table like the one shown in the "Value A" but final result shoud be 28,33 as in "Value B"

Category	Value A	Value B
A	10	10
B	30
B	35	35
C	40	40
	28,75	28,33

Thanks guys

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You could create a calculated column:

Value B =
IF (
    'Test data'[Value A]
        = CALCULATE (
            LASTNONBLANK ( 'Test data'[Value A], 1 ),
            ALLEXCEPT ( 'Test data', 'Test data'[Category] )
        ),
    'Test data'[Value A],
    BLANK ()
)

Then, insert a table visual, aggreate [Value B] with "Average".

Best regards,

Yuliana Gu

[Anonymous]

This looks preety good, thanks!

[Greg_Deckler]

How about:

Measure = AVERAGEX(Table,DISTINCT(Table[Column]))