cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## 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()

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?

1 ACCEPTED SOLUTION
Super User

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.

2 REPLIES 2
Regular Visitor

This is a wonderful book. It especially helps in writing Python script and Java scripts. Therefore, I am very grateful for useful materials that can help. Although, I encountered a different kind of task, and it caused me difficulties. This source helped me in preparing the work on swot analysis https://edubirdie.com/examples/swot-analysis/ I realized that the examples are just a godsend and they give a lot of fresh ideas and inspiration for writing.

Super User

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors