topic Re: Data Smoothing with Splines in R Script Showcase

Data Smoothing with Splines

Sharon — Wed, 16 Nov 2016 09:29:26 GMT

Description

Smoothing helps you to better visualize and understand noisy data

Prerequisites (The sample .pbix files will not work without these prerequites completed)

1. Install R Engine

Power BI Desktop does not include, deploy or install the R engine. To run R scripts in Power BI Desktop, you must separately installR on your local computer. You can download and install R for free from many locations, including the Revolution Open download page, and the CRAN Repository.

2. Install the required R packages.

Download the R script attached to this message and run it to install all required packages on your local machine.

Required R packages:

graphins, spline, scales

Tested on:

CRAN 3.3.1, MRO 3.3.0, powerbi.com

Legal Disclaimers:

Terms of Service and Third Party Programs.

Re: Data Smoothing with Splines

jdt008 — Mon, 06 Mar 2017 17:16:14 GMT

We love this visual. How should the script read if my dates are formatted as mm/dd/yyyy?

Thanks

Re: Data Smoothing with Splines

boefraty — Sun, 23 Apr 2017 06:56:55 GMT

Hi, sorry for the delay in reply.

The X column is of type "numeric". You can convert "date" type to "numeric" in Power Query. Next you need to take care of X-axis ticks labels, to be formatted as you want.

I did something similar in
https://community.powerbi.com/t5/R-Script-Showcase/ARIMA-forecasting/m-p/124237

Please have a look at the R code. If you still have trouble with it, you are welcome to send me the PBIX

Re: Data Smoothing with Splines

Anonymous — Fri, 10 Aug 2018 12:59:31 GMT

Been looking at this visualization lately as I need to plot some datapoints and need to add regression lines. I'm plotting revenue/sales per day, and using the date as the category/legend. However, I need one line for each of the days, instead of one for all the data points.

How much work am I looking at doing smoething like this in R?

Re: Data Smoothing with Splines

boefraty — Mon, 27 Aug 2018 10:53:23 GMT

Hi,

This is quite simple to achieve if you have basic expertise in R.

I'd also recommend you to look at R Custom Visual "Spline" it has regression lines

You can use it as a starting point.

Re: Data Smoothing with Splines

Anonymous — Mon, 27 Aug 2018 12:55:01 GMT

I actually wrote a script that kinda fixed what I was trying to do, it's not pretty, but it works. Problem I got now is that I'm trying to make a custom visual out of it so I can get the highlighting features you used working. I assume it was plotly? I got multiple regression lines working, but plotly isn't playing along. Tried the RADACAD tutorial a few times, but I'm guessing my code is written in a way that it isn't a simple copy paste job anymore.

library(ggplot2)
p = qplot(1:10, (1:10)^3)
g = ggplot(dataset, aes(x=Price, y=Production, color = Date))+
geom_point() +
theme(axis.text.x = element_text(size=14),
axis.text.y = element_text(size=14),
axis.title.x = element_text(size=16),
axis.title.y = element_text(size=16),
panel.background = element_rect(fill = "white",
colour = "777777",
size = 0.4, linetype = "solid"),
panel.grid.major = element_line(size = 0.08, linetype = 'dashed',
colour = "777777"),
panel.grid.minor = element_line(size = 0.08, linetype = 'dashed',
colour = "777777")) +
theme(legend.position="bottom") +
ylab("Production") +
xlab("Price") +
stat_smooth(method = "lm", formula = y ~ poly(x,2), size = 1, se = FALSE)
g$respect = TRUE
library(grid)
grid.draw(g)

Trying to get it working in this template.

############### Library Declarations ###############
libraryRequireInstall("ggplot2");
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################
g = qplot(Values$Data, data = Values, fill = Values$Type, main = Sys.time());
####################################################

############# Create and save widget ###############
p = ggplotly(g);
internalSaveWidget(p, 'out.html');

https://community.powerbi.com/t5/Developer/Converting-power-bi-R-script-visual-to-custom-R-visual/m-p/498110#M15275

Re: Data Smoothing with Splines

ropebender — Wed, 24 Apr 2019 16:24:27 GMT

I have a couple of visuals I want to use from R, but when I try to load this visual (and others) I am having issues trying to install the needed libraries I uninstalled the current version or R (3.5.1) and installed the version given in most all the visualizations in this section (3.3.1), but still I can't load most of the required libraries.

You say the visualization you require requires

Required R packages:

graphins, spline, scales

I can load scales, spline is not available for 3.3.1, but I installed splines2 which was available and I assume is similar enough thet I can make it work. However I don't know what to do with graphins. I can't find anything similar at CRAN, CRAN2, R-Forge, or rforge.net. Is there another repository that I should be using?

Thanks!

Re: Data Smoothing with Splines

Anonymous — Wed, 21 Aug 2019 13:41:57 GMT

hello!

is there a newer version of this? the script to install dependencies is no longer working.

Re: Data Smoothing with Splines

andymc123 — Thu, 19 Sep 2019 15:27:33 GMT

I have R installed, but I am a rookie. I was able to install all of the necessary packages so that the sample .pbix file provided here loads and works just fine for me. When I try to add the spline visual to my own .pbix file it doesn't want to accept measures in the X and Y axis. Message on the visual simply states "Need numeric X and Y variables". They are numeric, the Data Type is "Decimal Number", but formatted as "Percentage". Why wouldn't I be able to use measures? The data I have works just fine with the built in Power BI "scatter chart". What I really want is the line so that I can keep a National Average line on the visual and cross-highlight by districts to see the distance from a National Average.

Very cool visual, I hope that I can get it working.