Use R to highlight specific data point in Power BI

[v-alq-msft]

Scenario:

When using line charts to show the changes in different categories, we sometimes need to mark some special data, such as maximum and minimum values. However, in the default line chart in Power BI, we cannot set conditional formatting. Is there any other way?

Sample data:

Division	Year	Value
Division A	2016	84335
Division A	2017	89426
Division A	2018	93897.3
Division A	2019	150235.7
Division A	2020	180282.8
Division B	2016	87572
Division B	2017	92357
Division B	2018	129299.8
Division B	2019	193949.7
Division B	2020	155159.8
Division C	2016	74420
Division C	2017	141398
Division C	2018	155537.8
Division C	2019	202199.1
Division C	2020	242639
Division D	2016	93804
Division D	2017	73804
Division D	2018	125466.8
Division D	2019	188200.2
Division D	2020	207020.2

Preparation:

1. Install R.

We can download and install R for free from many locations, including the Microsoft R Application Network and the CRAN Repository.

2. Install needed packages in R.

• Launch your R application.
• Paste the code below to install packages and select one CRAN mirror closest. After clicking “OK”, the package will be installed. Then, we will get successful notification if the package is installed successfully.

Install.packages(“dplyr”)

• For other needed packages (gridExtra, ggplot2), repeat the step above.

3. Enable R visuals in Power BI Desktop.

For the details, please refer to this document: Create Power BI visuals using R.

4. Please note the restrictions mentioned in this official document.

Operations:

1. Create measures to calculate maximum and minimum values of each division.

MaxValue =
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Division] = MAX ( 'Table'[Division] )
    )
)

MaxValueYear = IF ( MAX ( 'Table'[Value] ) = [MaxValue], MAX ( 'Table'[Year] ) )

MinValue =
CALCULATE (
    MIN ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Division] = MAX ( 'Table'[Division] )
    )
)

MinValueYear = IF ( MAX ( 'Table'[Value] ) = [MinValue], MAX ( 'Table'[Year] ) )

2. Create an R visual.

• Put all needed columns and measures into “Values” field of the R visual.

• We can first look at how the generated dataset is.

# display the dataset

library(gridExtra)

  # gridExtra package provides a number of user-level functions to work with "grid" graphics, notably to arrange multiple grid-based plots on a page, and draw tables.

grid.table(dataset)

  # display dataset as a table

Note: Duplicated rows will be removed from the data. If you don’t want this, add a column with no duplicate values, such as an Index column.

• Then, we can achieve what we want like this:

# filter dataset with only MaxValueYear or MinValueYear

library(dplyr) # dplyr package is a fast, consistent tool for working with data frame like objects, both in memory and out of memory.
dataset_max<-dataset %>% filter(Year==MaxValueYear)  # filter dataset with only MaxValueYear
dataset_min<-dataset %>% filter(Year==MinValueYear)  # filter dataset with only MinValueYear

# plot a line chart

library(ggplot2)  # ggplot2 package is used to create Elegant Data Visualisations Using the Grammar of Graphics
ggplot(data=dataset,aes(y=Value, x=Year, colour=Division))+geom_line(stat="identity") +   # Create a line chart
geom_point(data=dataset_max, aes(x=MaxValueYear,y=Value),size = 5) +                     # add MaxValue points
geom_text(data=dataset_max, aes(label=Value),hjust=0, vjust=0, color="black")+            # add MaxValue label
geom_point(data=dataset_min, aes(x=MinValueYear, y=Value), size = 5)+                      # add MinValue points
geom_text(data=dataset_min, aes(label=Value), hjust=0, vjust=0, color="black")             # add MinValue label

Note: “hjust” and “vjust” are used to modify text alignment. These can either be a number between 0 (right/bottom) and 1 (top/left) or a character ("left", "middle", "right", "bottom", "center", "top"). There are two special alignments: "inward" and "outward". Inward always aligns text towards the center, and outward aligns it away from the center.

Now, let us test it.

It works great. Just try it!

Summary:

R has advantages in drawing static graphics and can produce publication-quality graphs. When we cannot achieve our needs in the default visualizations of Power BI, try to create an R visual, which will surprise us.

Author:  Icey Zhang

Reviewer: Ula Huang, Kerry Wang

Use R to highlight specific data point in Power BI - Icey.pbix