Use R to highlight specific data point in Power BI


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.

 

  1. 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”)

 

v-alq-msft_0-1610008599655.jpeg

 

 

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

 

  1. Enable R visuals in Power BI Desktop.

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

 

  1. 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] ) )

 

  1. Create an R visual.

 

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

v-alq-msft_1-1610008599664.jpeg

 

 

  • 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

3.jpg

 

 

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

 

4.jpg

 

 

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.

5.jpg6.jpg

 

 

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



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*