PowerBI Ratings Chart

In this post we are going to show how to create a cool looking ratings chart in Microsoft Power BI. The chart is below. The chart is best viewed in a touch screen (the vertical scroll bar would disappear).  Note: if you see a message "app.powerbi.com’s server IP address could not be found.", this is because the Power BI server is down temporarily. This has nothing to do with our website, but rather a Microsoft issue. Please refresh the page or come back after some time when the Microsoft server is back up.  To know how to create this visual, follow along the article.

If you don't already have Power BI Desktop installed, install it from the Microsoft store. Next, open Power BI on your system.

Load Data

We are going to use the 'The Best Phones for 2020' ratings which appeared on the PCMag website on 18th Feb 2020. The data is shown in the table below. Save this into an excel file and connect to this table using the 'Get data' button. Next load this table into Power BI.

Transform Data

We need to transform out data so that we can create our required visual. Press the 'Transform data' button to open the Power Query Editor window. In this Editor, go to the 'Add Column' tab and add a new column by pressing the 'Custom Column' button.  Call the new column as 'Path' and in the 'Custom column formula', enter '="1;2;3;4;5"' as the formula.

After adding the above column, the table should look like below in the Query Editor.

Next we are going to add 4 more rows for each phone, so that every phone has 5 rows in total. This is so that we can draw <=5 shapes for each phone in our ratings visual. To do this we are going to use a trick in Power BI. In the Power Query Editor go to 'Transform' tab. Select the 'Path' column and press the 'Split Column' -> 'By Delimiter' button. In the options which pop up, select 'Semicolon' as the delimiter. Next go the 'Advanced options' and select 'Split into' -> 'Rows' option.  You would see that for every phone, Power BI has entered 5 rows. Rows for the top 2 phones are shown below. The total number of rows would become 50 in the editor.

Now we are going to some columns which would define the shape in our chart for each path for every phone. For this we would use the Data Editor window as the formulae are much simpler there, just like in Excel. Save the Power Query window and chose apply changes. Navigate to the Data Editor window from the left hand side panel of Power BI. From the 'Table tools' tab, press the 'New column' button, to add a new column called 'Shape' with the following formula in the formula bar:

Shape = IF([Path]<=[Rating],1,IF(AND([Path] - 0.5 >= [Rating], [Path] < [Rating]+1),0.5,BLANK()))

Press the commit option from the left hand side of the formula bar to create the new 'Shape' column. Basically, what the formula does is to add a full shape (1) for a path if the path value is less than the rating and a half shape (0.5) if the rating is half less than the path value. If the path value is greater than the rating by 1 or more it does not add a shape for that path, i.e. a Blank value.

ARTICLE IN PROGRESS