Line chart with range of values in Power BI

[v-yiruan-msft]

Scenario:                                                             

Sometimes we will encounter requirements not only to aggregate and analyze the trend of data, but also to know whether the value at a certain point is out of the normal range. Generally speaking, we will prefer “Area Chart” visual to achieve this outcome. But when the minimum value is not zero, the obtained result will not meet our requirements anymore. Unfortunately, there is currently no feature to add a range of value to a polyline in Power BI. I tried the “Stacked Area Chart” visual, after a series of parameter modifications, finally got the chart be closest to the ideal result. But it is still less than satisfactory.

After doing some research, I found that when combining R and Power BI, the better result can be achieved. The process of my test is as follows.

Sample data: 

Expected output:

Method 1: Using “Stacked Area Chart” visual in Power BI Desktop

1. Create a “Stacked Area Chart” visual and sort the field

2. Change “Data colors” in “Format” pane.

Set the color of “Min” field as “White”;

Set the color of “Max” same as the color of “Value”.

It is clear that the boundary of the "Max" column cannot be removed.

Method 2: Using Polygon() function in R

Preparation: 

1. Install R: we can download and install R for free from the Microsoft R Application Network or the CRAN Repository.  (Latest version: R-4.0.3 for Windows (32/64 bit))

2. Install needed packages in R. 

• Launch R application. 
• Secure one CRAN mirror and choose needed packages. Or you can paste the code below to install packages and select one CRAN mirror closest. 

         install.packages("reshape2")

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

4. Please check the restrictions and limitations mentioned in this official document. 

Operations:

1. Import the sample data into Power BI Desktop

2. Create an R visual. 

• Put all columns needed into “Values” field of the R visual. 
• We can look at the results obtained by using the function of polygon().

          #Reimport the data and set the value of the first column as a row variable

  • a<-read.table("your file address",sep=",",header=T)
  • xx<-c(min(a$Date):max(a$Date),max(a$Date):min(a$Date)) #Create a vector
  • yy<-c(a$Min,rev(a$Max))  #Create the other vector
  • plot(a$Date,a$Value,type = "o",col ="blue",ylim=c(0,50),xlab="Year",ylab="Value") #create a line chart
  • polygon(x = xx,                           # X-Coordinates of polygon
  •         y = yy,                                # Y-Coordinates of polygon
  •         col = rgb(255,0,0,50,maxColorValue=255),         # Color of polygon

          border = "NA")                                   # Color of polygon border
  

Summary: 

It is obvious from the results of the two images that R has certain advantages in adding range areas. Not only can it draw a specified range, but it can also create a confidence interval range with a percentage.

Hope that Power BI will introduce corresponding functions soon in order to make data analysis more convenient for people.

Author: Yuna 

Reviewer: Ula Huang, Kerry Wang

Line chart with range of values in Power BI.pbix