Creating a histogram over a Power BI measure

Question

Since Microsoft decided to deprecate the Histogram visualization in Power BI it became a problem to visualize distributions in this application.

I solved this problem in the case where the histogram is over a table column: using a clustered column chart with the value as the X axis and the count as the Y axis (It is also possible to add range bins to group together different values).

However, if I have a measure, this approach will not work. For example, If I have a table of movie ratings (with columns 'movieId', 'userId', and 'userMovieRating') I don't know how to create a histogram of the average ratings for each movie. If I try the approach that was described above, Power BI will not even let me use the measure in the X axis.

sample data:

movieId	userId	rating
m1	u1	5
m1	u2	3
m2	u1	2
m2	u3	4
m2	u4	3
m3	u2	3
m3	u3	3
m3	u5	3

expected output:

Average rating	number of Movies
4	1
3	2

Can you suggest a way to achieve this without creating additional tables?

Answer 1

movies table looks like this as per your sample data.

Create a measure as follows:

Ave Rating = AVERAGE(movies[rating])

Create a new disconnected table as follows:

Average Ratings = 
VAR maxRating = MAXX(VALUES(movies[movieId]), [Ave Rating])
RETURN GENERATESERIES(0,maxRating,1)

which produces the following:

You need to use generate series for a proper histogram as some averages (0,1,2) will have a count of zero and you want these to show too.

Create a column chart. On the x axis, add the column from your newly created table ensuring show all values is selected.

On the y axis, add the following measure:

Measure = 

VAR cursor = SELECTEDVALUE('Average Ratings'[Value])

VAR segment = 
FILTER(
    ADDCOLUMNS(
        SUMMARIZE(movies, movies[movieId]),
        "@aveRating", [Ave Rating]
    ),
    [@aveRating] = cursor
)
    
RETURN COUNTROWS(segment) 

Finished result: