Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Categorizing book ratings

Hi! I'm new to PowerBI.

I have a dataset of books, one column including the user ratings for each book. 

I've written a Python script which categorizes these ratings in the following way:

 

 

import matplotlib.pyplot as plt
dataset.dropna(0, inplace=True)

def segregation(data):
    values = []
    for val in data.average_rating:
        if val>=0 and val<=1:
            values.append("Between 0 and 1")
        elif val>1 and val<=2:
            values.append("Between 1 and 2")
        elif val>2 and val<=3:
            values.append("Between 2 and 3")
        elif val>3 and val<=4:
            values.append("Between 3 and 4")
        elif val>4 and val<=5:
            values.append("Between 4 and 5")
        else:
            values.append("NaN")
    return values

dataset['Ratings_Dist'] = segregation(dataset)
ratings_pie = dataset['Ratings_Dist'].value_counts().reset_index()
labels = ratings_pie['index']
colors = ['blue','purple', 'orange','red', 'black']
percent = 100.*ratings_pie['Ratings_Dist']/ratings_pie['Ratings_Dist'].sum()
fig, ax1 = plt.subplots()
ax1.pie(ratings_pie['Ratings_Dist'],colors = colors, 
        pctdistance=0.85, startangle=90, explode=(0.05, 0.05, 0.05, 0.05, 0.05))
centre_circle = plt.Circle((0,0), 0.70, fc ='white')
fig1 = plt.gcf()
fig1.gca().add_artist(centre_circle)

plt.show()

 

 

 

This results in the following chart: 

 

 

 

 

 

 

But I'd like to display the following on the chart written besides each color and their percentages too:

 

 

Avg rating distribution
Between 4 and 5 - purple
Between 3 and 4 - blue
Between 2 and 3 - orange
Between 1 and 2 - black
Between 0 and 1- red

 

 

 

Goal is to see how many users gave a rating between x and y for each number interval.

How can I accomplish this in PowerBI? 

  • You need to create a new column on your table for the rating group. You can either do that by adding a conditional column in Power Query or you can create a calculated column using the below DAX

    Rating group = SWITCH( TRUE(),
    'Table'[average_rating] >= 0 && 'Table'[rating] <= 1, "Between 0 and 1",
    'Table'[average_rating] > 1 && 'Table'[rating] <= 2, "Between 1 and 2",
    'Table'[average_rating] > 2 && 'Table'[rating] <= 3, "Between 2 and 3",
    'Table'[average_rating] > 3 && 'Table'[rating] <= 4, "Between 3 and 4",
    'Table'[average_rating] > 4 && 'Table'[rating] <= 5, "Between 4 and 5"
    )

    Then create a measure to count the number of occurences, 

    Num occurences = COUNTROWS('Table')

    and create a donut chart with the measure as the value and the new column as labels.

1 Reply

  • You need to create a new column on your table for the rating group. You can either do that by adding a conditional column in Power Query or you can create a calculated column using the below DAX

    Rating group = SWITCH( TRUE(),
    'Table'[average_rating] >= 0 && 'Table'[rating] <= 1, "Between 0 and 1",
    'Table'[average_rating] > 1 && 'Table'[rating] <= 2, "Between 1 and 2",
    'Table'[average_rating] > 2 && 'Table'[rating] <= 3, "Between 2 and 3",
    'Table'[average_rating] > 3 && 'Table'[rating] <= 4, "Between 3 and 4",
    'Table'[average_rating] > 4 && 'Table'[rating] <= 5, "Between 4 and 5"
    )

    Then create a measure to count the number of occurences, 

    Num occurences = COUNTROWS('Table')

    and create a donut chart with the measure as the value and the new column as labels.