Aim
To integrate R with Power BI, use R for statistical analysis, advanced visualization and timeseries forecasting, and combine the results with Power BI KPI cards, slicers, filters and drilldown in one professional, interactive dashboard.
Objectives
Set up R with Power BI and enable R script visuals.
Import and clean the Adventure Works data and calculate summary statistics.
Create at least four R visuals, five KPI cards and four native Power BI visuals.
Run an advanced analysis (a time-series forecast) with an R package.
Add slicers, filters and a drill-down, then write the business insights.
Tools: Power BI Desktop, R (with RStudio for testing), packages ggplot2 , forecast and scales.
Dataset: Adventure Works sales, product, territory and customer files.
The short version
- Power BI is excellent at totals and slicers but cannot draw a box plot or fit a forecasting model on its own. R can. I connected R to Power BI and built a dashboard that answers one business question: where is Adventure Works sales heading? The history is $24.91M of sales and $10.46M of profit (42.0% margin) from January 2020 to June 2022. Monthly sales climbed from about $0.5M to $1.83M, and an ARIMA model projects them to keep rising over the next six months, with a wide band of uncertainty around that path.
Why bring R into Power BI?
Power BI’s built-in visuals cover the everyday cases: totals, trends, rankings, shares. They do not show the distribution of a value, test a statistical idea or predict the future. R fills that gap with thousands of packages, and Power BI can run an R script and show the resulting picture as a normal visual on the page.
An R visual works like this. You drop fields into the visual, Power BI hands those columns to your script as a data frame called dataset , the script draws a plot, and Power BI displays the plot. Because the visual is part of the report, slicers and filters still change it. Two details matter. First, duplicate rows are removed from dataset unless the fields include something unique such as OrderNumber and OrderLineItem . Second, an R visual re-runs its script on every filter change, so it is slower than a native visual and the output is a static image, not a clickable chart.
The model I used for forecasting is ARIMA (AutoRegressive Integrated Moving Average). It predicts the next value of a series from its own past values and past errors. auto.arima() from the forecast package tries many combinations and keeps the one with the best fit, and forecast() then projects it forward and adds prediction intervals.
Task 1: Setting up R with Power BI
- Install R from CRAN and, optionally, RStudio. Install the packages once from the R console: install.packages(c("ggplot2","forecast","scales"))
- In Power BI Desktop open File > Options and settings > Options > R scripting. Check that the R home directory points to the R installation, and set RStudio as the external editor if wanted.
- Switch on Enable script visuals when Power BI asks, and add the R visual to the page from the Visualizations pane (the R icon).
Task 2 and 3: Importing and cleaning the data
The data comes as CSV files. In Power Query I appended the three yearly sales files, joined them with the product, subcategory, category, territory and customer tables, and kept one
flat table with one row per order line (56,046 rows). Sales is not stored in the files, so I calculated it as OrderQuantity × ProductPrice , and cost and profit in the same way.
The data stops on 30 June 2022, so all analysis uses January 2020 to June 2022. The final query in Power Query looks like this.
Task 5: Summary statistics
Before drawing anything I looked at the numbers behind the sales column. In R the one-liner is summary(dataset$Sales) ; the results for the 56,046 order lines are below.
The mean is more than twelve times the median and the standard deviation is almost twice the mean. That tells me the data is heavily right-skewed: most order lines are small accessories, and a minority are expensive bikes that pull the average up. It also explains why a log scale is needed on the plots below.
Task 4 and 6: The R visuals
Every R visual below uses fields from the same flat table, and each one is a short ggplot2 script.
R visual 1: Histogram of order-line value
Fields used: Sales . Power BI shows the warning “Duplicate rows will be removed from the data” above the script, which is why unique keys such as OrderNumber and OrderLineItem are worth adding when every order line must count.
The histogram and its script in Power BI’s R script editor. Two separate clusters appear: a large group of small accessory and clothing lines around $10 to $60, and a smaller group of bike lines between about $500 and $3,500.
R visual 2: Box plot of sales by category
Fields used: CategoryName , Sales.
Sales per order line by category. Bikes sit one to two orders of magnitude above accessories and clothing, and the three boxes barely overlap.
R visual 3: Monthly sales with a smoothed trend
Fields used: Month , Sales . The script adds up sales per month and fits a LOESS smoother, shown as the dashed line.
Monthly sales (red) and the LOESS trend (dashed). Sales slid from about $0.6M in early 2020 to a low near $0.33M in November 2020, stayed flat through mid-2021, and then accelerated to $1.83M in June 2022
R visual 4: Six-month ARIMA forecast
Fields used: Month , Sales . This is the advanced analytics task, covered in detail next.
Task 7: Advanced analytics, a time-series forecast
The forecast script does five things.
- Adds up sales per month and sorts them by date.
- Builds a monthly time series with ts() , using frequency = 12 and a start year and month taken from the data.
- Lets auto.arima() pick the model orders.
- Projects six months ahead with forecast(..., h = 6).
- Draws the history and the forecast with autoplot().
Screenshot 1: steps performed. The forecast visual with its R script in the script editor. The blue line is
the point forecast, the dark band is the 80% interval and the light band the 95% interval.
How to read the forecast
- Direction: the forecast starts at roughly $1.9M for July 2022 and rises to roughly $2.4M by December 2022, a continuation of the upward trend.
- Uncertainty: the band fans out with every month. By December the 95% range runs from about $1.6M to $3.2M. The model is confident about next month and honest about how little it knows about month six.
- Limits: the series is only 30 months long, which is short for a seasonal model, and December 2021 was a visible spike of $1.64M. With so little history the model cannot separate a true December effect from a one-off, so the forecast mostly follows the recent trend. It should be read as a planning range, not a promise.
Task 8: Building the interactive dashboard
Layout
- Title banner across the top: “Adventure Works: Sales Analytics with R and Power BI”.
- Five KPI cards: Total Sales, Total Profit, Total Orders, Total Customers and Profit Margin %.
- Three slicers: Region, Year and CategoryName. They filter every visual on the page, R visuals included.
- Four native visuals: a line chart of sales by year and month, a column chart of sales by category with drill-down, a donut chart of profit by category and a bar chart of sales by country.
- Four R visuals: the histogram, the box plot, the monthly trend and the forecast, on pages 3 and 4.
The pages I built
The KPI measures
Screenshot 2: output through cards. Five KPI cards (25K orders, 41.97 profit margin %, 17K customers, $10.46M profit, $24.91M sales) with the Region, Year and CategoryName slicers.
The Power BI visuals: sales by year and month with drill-down, sales by category, profit by category and sales by country.
Filters and drill-down
- The Sales by Year / Month line chart uses a date hierarchy, so the drill arrows move from the yearly view to the monthly one.
- The Sales by Category (drill down) column chart is built on a hierarchy that starts at category, so it can be drilled into finer levels.
- The Region, Year and CategoryName slicers filter the visuals on the page. Choosing “Bikes” or “2021”, for example, redraws the charts and the R visuals together.
R visuals page, part one: histogram and box plot as report visuals.
R visuals page, part two: monthly trend and the six-month forecast.
Task 9: Business insights
- Bikes carry the business
Bikes are 94.9% of sales ($23.64M) and 93% of profit ($9.73M). Accessories add only $0.57M of profit (5.45%) and clothing $0.16M (1.55%), as the donut chart shows. A small change in bike demand moves the whole company.
- Growth is real, and it began in mid-2021
Yearly sales were $6.40M in 2020 and $9.32M in 2021, and the first half of 2022 alone reached $9.19M. The trend line shows the change clearly: flat at about $0.5M a month until June 2021, then a steady climb. Accessories and clothing started selling in July 2021, which
coincides with the jump.
- Order values are split into two worlds
The histogram and box plot show two kinds of sale: many small lines of $10 to $60 and fewer large bike lines of $500 to $3,500. Averages hide this. The mean line value is $444.54, yet the median is only $34.99. Decisions about average prices or discounts should always be made per category.
- Geography is concentrated
The United States leads with $7.94M and Australia is the biggest single region at $7.42M. Canada is the smallest of the six country markets at about $1.8M, and Germany, France and the United Kingdom each sit between $2.4M and $2.9M.
- The forecast says “keep growing”, with caution
The model expects monthly sales to rise from about $1.9M to $2.4M by December 2022. The widening band means inventory and staffing plans should be set against the lower and middle parts of the range and reviewed monthly as actual figures arrive.
What I would do next
- Plan stock around the December peak and the January dip seen in both years.
- Cross-sell accessories with bike orders, since they are cheap and high margin.
- Re-run the forecast every month and compare it with actuals to see whether the model needs a seasonal term once more history exists.
Task 10: Conclusion
This practical showed me what each tool is best at. Power BI handled the KPIs, slicers and drill-down well, and R handled the distribution plots and the forecast that Power BI cannot do natively. Getting the two to work together took more care than I expected: the R path had to match the installed libraries, duplicate rows are silently removed unless unique keys are included, and R visuals refresh more slowly than native ones. The final dashboard combines five KPI cards, four native visuals and four R visuals, shows a business that grows fast and depends on bikes, and gives a six-month outlook with an honest measure of uncertainty.
Top comments (0)