Optimization Problem in Power BI using R scripts: Part 3



As I have discussed before, R is not only for doing Machine Learning (despite it is a really good tools for it), it also can be used for helping managers to so decision support. In the last two posts (Post 1 and 2), I have explained the main concepts behind the optimization problems. Also, how to write and solve these problems using simple R codes inside R studio. In this post, I am going to show how to solve an optimization problem  inside Power BI using R scripts and visualization. Imagine that I have below information about  production line of Product 1 and Product 2 (see post 2).   all data As I have explained before, imagine we produce Product 1 and 2.  The cost of producing product 1 is 20 and product 2 is 60 (cost column in above table), Also we have to spend 5 hour and 10 hours to produce product 1 and 2 (second column) and  so forth (I have explain how to solve the problem in Post number 2), I have shown the related R codes for doing the optimization. as below:

I also created an R visual in Power BI, as you can see in the below picture, I have selected the the data from dataset field. and put the R codes inside it rcodes1 However, if I run the code, it does not show anything! in R visual in Power BI, we have to an image output from our codes otherwise we got the below error :as ” there is no visual showing up” rcodes2 Now to show the result, I want to create a table to show the result of optimization. So, I use a package like “gridExtra” and “grid”. this package helps us to show the result in as a visual. The result of the algorithm has been put in “Prod.sol” variable.  the number cost of the producing product 1 and 2 has been stored in prod.sol$objective, also prod.sol$solution stores information about the amount of product that we should produce for product 1 and 2.

moreover, the profit has been stored in: result$prod.sol.objective*result$prod.sol.solution

all of these attributes have been loaded into a variable named “result”. I used the grid.table function to draw the result of optimization as below : result As we can see in the above picture, we have to produce 20 tonne of product 1 to achieve 400 dollar profit, and also we have to produce 75 tonne of product 2 to achieve 4500 dollar profit.

In total, we will recived profit 4900 dollar.

This is a very simple example of how to optimization inside Power BI. We also able to do if then analysis to check if we have more of product 2 and less of product 1 how much profit we will get.

Leila Etaati
Dr. Leila Etaati is Principal Data Scientist, BI Consultant, and Speaker. She has over 10 years’ experience working with databases and software systems. She was involved in many large-scale projects for big sized companies. Leila has PhD of Information System department, University of Auckland, MS and BS in computer science. Leila is Microsoft Data Platform MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *