Averaging for Unique/Distinct values only

[RustyNails]

Hello, I have a fairly simple problem but can't seem to work my way around it.

I have an Product table, and I want to uniquely average it's rating across the table. What I mean is that if a rating repeats for different markets or departments, it should not impact it's overall rating. Business reason is that the product's review is not based on it's market performance. So a product for example could theoretically repeat itself (and it's rating) for all 50 states with one department. But for another department, it could be only one record. Yet we want the average to be based on the distinct rating, regardless of who reviewed it and howmany times its repeating. Market column is simply there to show that the product is available. Here's what the Data looks like (below). MyAverage is obviously incorrect. I want it to match Expected Average.

[Zubair_Muhammad · Accepted solution]

HI @RustyNails

Try this MEASURE

Measure =
AVERAGEX (
    SUMMARIZE ( Table1, Table1[Product Name], Table1[Rating] ),
    CALCULATE ( VALUES ( Table1[Rating] ) )
)

[RustyNails]

Worked. Thank you!