Power BI average rating per product

Question

I have a Ratings table with some product ratings:

productKey   rating
product-1    4
product-1    5
product-2    3

I want to calculate the average rating per product:

Incorrect average (default): (4+5+3)/3 = 12/3 = 4
Average per product : ( (4+5)/2 + 3 ) / 2 = (4.5 + 3) / 2 = 3.75

I managed to do this with an intermediate table

1) Create table which averages ratings per product:

RatingsPerProd = SUMMARIZE(Ratings,Ratings[productKey],"averageRating",AVERAGE(Ratings[rating]))

which creates the following table:

productKey   averageRating
product-1    4.5
product-2    3

2) Then I simply do an AVERAGE of averageRating

However I'd like to do this with 1 single measure on the original Ratings table, but whatever I try to do with the SUMMARIZE formula I get the following error:

The expression refers to multiple columns... cannot be converted to a scalar value

How can I achieve average per product in 1 single measure on the original Ratings table?

TJ_ TJ_ · Accepted Answer · 2018-04-04T10:32:32

You can try the following formula:

AveragePerProduct =
AVERAGEX (
    VALUES ( Ratings[productKey] ),
    CALCULATE ( AVERAGE ( Ratings[rating] ) )
)

Result:

Power BI average rating per product

1 Answers