Calculate distinct

[abukapsoun]

Hi,

I need your expertise please

I have the following table

HC	Price
X	350
X	350
X	350
Y	400
Y	400
Z	600
Z	600

How can I compute the average price taking into consideration distinct HCs and not all of them?

Thanks,

[amitchandak]

@abukapsoun , Try a measure like

averageX(values(Table[HC]), max(Table[Price]))

or

averageX(Summarize(Table, Table[HC],"_1", max(Table[Price])), [_1])

[abukapsoun]

Hi,

both did not work

[amitchandak]

@abukapsoun , expected output.

refer if file after signature can help

[abukapsoun]

I am expecting (350+400+600)/3

[amitchandak]

@abukapsoun ,Please find the attached file after the signature . I think that 450 . refer Avg2, Avg 3

[abukapsoun]

Still not working 😞

[Greg_Deckler · Accepted solution]

@abukapsoun - Seems like this should be:

Measure 29 = 
    VAR __Table = SUMMARIZE('Table (29)',[HC],"Average",AVERAGE([Price]))
RETURN
    AVERAGEX(__Table,[Average])

Attached PBIX below sig, Page 29.

[amitchandak]

@abukapsoun , what is not working, I can see 450 in card, do you need that at each line ?

Calculate(averageX(Summarize(Table, Table[HC],"_1", max(Table[Price])), [_1]) , all(Table))