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.
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.
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
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.