Power BI Advanced Visualizations & R : Circular BarPlot with R Script Visual.

 

In the video I have shared a method to draw the circular barplot in power BI desktop file. As shown in the screen, the Circular BarPlot, which wraps a barplot that has larger numbers of categorical entities in circular shape, thus with it users can obtain all entities with their measure in a more eye catching organization space chart.

To get this Circular Barplot chart in power BI we need to employ the power BI R script visual. 

To Enable R scripts Visual feature in Power BI Desktop , we need go to File > Options and settings > Options > R Scripting.

In the R scripts options you have two fields to fill. First one is to choose home directory for R, which means you need first to install a R engine and select the installed R engine location for the field. To install R engine we may click the  link ”how to install R”. it brings us to the Microsoft learning site regarding where to download R engine and how to install it. 

The second field is to choose which R IDE(Integrated Development Environment) ,for example the R Studio, which is the R IDE you want Power BI desktop to launch, of course you also need to install R Studio, to know what benefit we need R studio and how to install R studio, click the link “know more about IDEs”. It will bring us to the site to know the benefits by using R IDEs,  where to find and how to install R Studio. I personally like very much the Feature “ Edit script in external IDE “. As it can have our power BI desktop data automatically imported and displayed in the R IDE. From there, we can modify the script in external R IDE, then paste it back into Power BI Desktop to create Power BI visuals and reports. 

Once above installation and setting done, we can start to work on R scripts creation for drawing the circular bar plot in power BI desktop. we can work on the scripts directly in Power BI R script visual or we use the external R studio to create R scripts, then copy and paste the scripts into power BI R scripts visual.  R studio is IDE and has many interactive features to support R scripts creation. As shown in my example. I have source panel to edit and run R script, I have environment panel to show my available variables, In below the panel has the tabs for help Packages and Plots among others. In left side I have console panel to see the result of code I run in source panel, I can also run codes in this console panel. In this video I shall demo the way by using external R studio to create the R scripts to draw the circular bar plot.

Back to my power BI desktop file, in table view, I have an Employee_skills_evulations table which imported from my excel file in hard drive. In the original table we have the first column lists 13 employees, the column 2 to 5 have the corresponding evaluated scores listed from 1 to 5 regarding four criteria: technical skills, soft skills, leadership & management, Personal development for each employee. By few power query transformation steps we reached a unpivot tabular table , which has only two columns besides column employee list, the attributes column covers all four evaluation criteria, the Value column includes all score values.”

Now we are ready to use R script visual to draw circular barplot for the dataset. In report view add a new empty report page, name it as “Circular Barplot for Youtube Demo”,  add an R visual by clicking on "R Script" in the "Visualizations" panel. In Data “fields” panel, drag and drop all there columns of table“employees_skills_evualations’ onto the "Values" area of the R visual.

We see notes in R scripts visual tell that the table data is loaded in the created variable dataset.

As mentioned before that I shall use the external R IDE, i.e.: R studio to create the R scripts to draw the circular bar plot. 

Let’s launch our installed R studio directly from Power BI Desktop. Just Select the Edit script in external IDE button.

In the launched R studio, we see three lines of the script that Power BI Desktop automatically generated, those lines do import the source data table to R studio from Power BI Desktop.

Let’s run the power BI generated code line, and in console panel use the View function check if the data imported correctly. A table view is visible for us to check in Source panel. 

At This phase we can start to create our scripts to draw the Circular Barplot.

The First Step is rename the dataset source by variable data

data <- dataset

Run the code, we can check the transferred source data table by Viewing function in console panel. Yeah that is we expected, in environment panel we see the Data variable which has 52 observations of 3 variables.

The Second step is loading the related plot packages, ensure source data type, and initialize value for variable. Run the codes for Loading packages:

Library(dplyr)

library(ggplot2)

Ensure the category data type is factor and initial value for variable text_hjust which will be used for text label horizontal position justification:

data$Attribute <- as.factor(data$Attribute) 

text_hjust=NULL

Third step : to separate each attribute group of bar, we need to add empty bars to each attribute group, 

let’s define empty bars data frame with 3 empty bars:

empty_bar <- 3

then create an empty data frame which has same structure as source data:

to_add <- data.frame( matrix(NA, empty_bar*nlevels(data$Attribute), ncol(data)) )

colnames(to_add) <- colnames(data)

to_add$Attribute <- rep(levels(data$Attribute), each=empty_bar)

add the empty data frame to source data frame: 

data <- rbind(data, to_add)

Check the Data again. Yes  3*4 , that should be 12 empty bars added in the source data table

Sort the attribute groups and then values:

data <- data %>% arrange(Attribute,Value)

check the data, that is sorted !

Add id for each value row: 

data$id <- seq(1, nrow(data))

Check if IDs were added in data source.

The fourth step: Define the y position of each label

creating a new data frame label_data and assigns the contents of variable data : 

label_data <- data: 

calculating the total number of rows in the label_data data frame and assigns it to the variable number_of_bar.: 

number_of_bar <- nrow(label_data)

Calculating the angle for labeling data points. It uses a formula that depends on the id column values and the total number of rows in the data frame. The angle is used to position labels for data points. substracting 0.5 from Id because the letter must have the angle of the center of the bars. Not extreme right(1) or extreme left (0):

angle <- 90 - 360 * (label_data$id-0.5) / number_of_bar 

Set the hjust (horizontal justification) column in the label_data data frame. It assigns a value of 1 to hjust if the angle is less than -90, and 0 otherwise. This effectively controls the horizontal alignment of the labels.

label_data$hjust <- ifelse(angle < -90, 1, 0)

Update the angle column in the label_data data frame. If the angle is less than -90, it adds 180 to the angle. This is used to control the angle at which the labels are displayed. The purpose is to make sure that the labels are readable and properly oriented.

label_data$angle <- ifelse(angle < -90, angle + 180, angle)

let’s do quick check to the variable “ label_data$hjust” and the variable “label_data$angle”

The fifth step: prepare a data frame for base lines.

This part groups the data data frame by the "Attribute" column. Subsequent operations will be performed within each group defined by unique values in the "Attribute" column.

data %>% group_by(Attribute) %>% 

Within each group, this summarizes the data. It calculates the minimum value of "id" as "start" and subtracts the "empty_bar" value from the maximum value of "id" as "end".

summarize(start = min(id), end = max(id) - empty_bar)

The rowwise() function prepares the data for row-wise operations. Then, it calculates the "title" as the floor of the mean of "start" and "end" for each row within the group.

rowwise() %>% mutate(title = floor(mean(c(start, end)))

This part performs an inner join between the label_data data frame (with only the "id" and "angle" columns selected) and the base_data based on matching values between "title" and "id." This operation combines the two data frames based on these columns.

inner_join(label_data %>% select(id, angle), by = c("title" = "id"))

Finally, it calculates the "angle" column. If the "angle" falls within the specified ranges, it is adjusted by adding or subtracting 90 degrees.

mutate(angle = ifelse((angle > 0 & angle <= 90) | (angle > 180 & angle <= 270), angle - 90, angle + 90))

The sixth step: This is a key step to prepare plot based on power BI slicers dynamic selection for Attribute group. There are two general scenarios:  more than one attribute group selection and only one attribute selection:

If there are more than one attribute group selected...

  if ( nrow(base_data) > 1 ) {

    then prepare a data frame for grid 

    Copy the contents of the 'base_data' data frame into variable 'grid_data' 

    grid_data <- base_data

    Adjust the 'end' column values by shifting them up by one row

    grid_data$end <- grid_data$end[c(nrow(grid_data), 1:nrow(grid_data) - 1)] + 1

    Adjust the 'start' column values by shifting them down by one row

    grid_data$start <- grid_data$start - 1

    Remove the first row from the 'grid_data' data frame

    grid_data <- grid_data[-1,]

 Then Prepare the plot for this scenario with adding grids

  p <- ggplot(data, aes(x=as.factor(id), y=Value, fill=Attribute)) +       

The id is a factor. If x is numeric, there is some space between the first bar:

    geom_bar(aes(x=as.factor(id), y=Value, fill=Attribute), stat="identity", alpha=0.7) +

  Add the a value for 5/4/3/2/ indicators:

    geom_segment(data=grid_data, aes(x = end, y = 5, xend = start, yend = 5), colour = "blue", alpha=1, linewidth=0.5 , inherit.aes = FALSE ) +

    geom_segment(data=grid_data, aes(x = end, y = 4, xend = start, yend = 4), colour = "blue", alpha=1, linewidth=0.5 , inherit.aes = FALSE ) +

    geom_segment(data=grid_data, aes(x = end, y = 3, xend = start, yend = 3), colour = "blue", alpha=1, linewidth=0.5 , inherit.aes = FALSE ) +

    geom_segment(data=grid_data, aes(x = end, y = 2, xend = start, yend = 2), colour = "blue", alpha=1, linewidth=0.5 , inherit.aes = FALSE ) +

   Add text showing the value of each 5/4/3/2 lines

    annotate("text", x = rep(max(data$id),1), y = c(2, 3, 4,5), label = c('2', '3', '4', 5) , color="blue", size=6 , angle=0, fontface="bold", hjust=1) 

  } 

else {

    else the other scenario is there is only one attribute group selected...

    this time prepare the plot without the grids:

    p <- ggplot(data, aes(x =as.factor(id), y = Value, fill = Attribute)) +

      

     Add a barplot:

      geom_bar(stat='identity', alpha=0.5) +

      

      Add text showing the value of each 5/4/3/2 lines:

      annotate('text', x = rep(max(data$id), 1), y = c(2, 3, 4, 5),

               label = c('2', '3', '4', 5) , color='blue', size=4,

               angle=0, fontface='bold', hjust=1)

  }

  

Run this big conditional codes for preparing data frame for grid. We see the grid data variable in environment console. 

Now add all the other features to the plot:

  Calculating the Number of distinct groups:

  num_group_elements <- length(unique(data$Attribute))

Define text horizontal justification if not passed as parameter, 

if text_hjust is NULL, text_horiz_justification will be a vector of 0.5 values.

If (is.null(text_hjust))

{  text_horiz_justification <- rep.int(.5, num_group_elements)

  } 

otherwise text_horiz_justification will be simple assigned the value of text_hjust. This is a way to provide a default value of 0.5 for text_horiz_justification when text_hjust is not specified or is NULL.

else {

  text_horiz_justification <- text_hjust

  }

At this phase Let’s try to plot the graphic by running the code “P”. We get a stack barplot.

Last step :  convert the bar plot to Circular Bar plot

This is the section for making circular plot:

p <- p +

which we need additionally to set the y-axis limits for the plot, restricting it to the range between -5 and 6:

ylim(-5,6) +

  

apply a minimalistic theme to the plot:

theme_minimal() +

further customizes the plot's theme. It removes the legend, axis labels, grid lines, and adjusts the plot margins:

theme(

    legend.position = "none",

    axis.text = element_blank(),

    axis.title = element_blank(),

    panel.grid = element_blank(),

    plot.margin = unit(rep(-1,4), "cm") 

  ) +

  

change the coordinate system of the plot to polar coordinates, which is often used for creating circular bar plot:

coord_polar() + 

  geom_text(data=label_data, aes(x=id, y=Value+0.1, label=EmployeeID, hjust=hjust), color="black", fontface="bold",alpha=0.9, size=6, angle= label_data$angle, inherit.aes = FALSE ) +

  

Add text labels to the plot using data from the label_data data frame. It specifies the aesthetics for the text labels, including their position, color, font style, alpha (transparency), size, and angle:

  geom_segment(data=base_data, aes(x = start, y = -1, xend = end, yend = -1), colour = "black", alpha=0.8, size=0.6 , inherit.aes = FALSE )  +

  geom_text(data=base_data, aes(x = title, y = -2, label=Attribute), hjust=c(0.5,0.6,0.4,0.4), colour = "black", alpha=0.9, size=4.5, fontface="bold", inherit.aes = FALSE)

Now let’s draw plot by running the last step scripts, we see the expected circular barplot, there are four lines warning messages which are related to the 12 empty bars we have added before in data.

We need to copy the Scripts we created in R studio and paste theme into our previous Power BI R visual editor , run the scripts . we see the expected circular in R scripts visual.

Now we may adjust some parameters in power Bi scripts editor to see the changes 

For example: 

- check and change the empty bar number 3 to 4, we see more space between group bar. we do not need this change and put it back.

- Change alpha = 0.7 to 0,4 to set the transparency of the bars. we see more transparency on the bar filling colour. We do need more colour and set it back. 

Let’s add slicer for the circular barplot visual to change the data view interactively. 

- Remove title for circular barplot chart

- Adjust the position of circular barplot, Add slicer visual in the report page.

- Drag and drop the group variable Attribute into Field,

- In the slicer settings option, change the “vertical list” to “Tile”

- In the selection setting , switch on the “select all”

- Adjust the position of slicer to the top position in the page.

- Check the circular barplot change by selecting different attribute combitations.   

- Save the power BI report file

We can publish the desktop file to power BI service. 

That is all about in this video, how to draw a circular bar plot via Power BI R scripts Visual. The introduced method covers : how to enable R scrip visual in power BI, how to launch R studio from power BI directly with automatically imported source data, and how to create R scrips for drawing circular barplot in R studio , and how to copy the R scripts and paste it back into power BI R script visual for drawing circular barplot. Hope my this video can bring some helps for your power BI advanced visualizations