Optimization Problem in Power BI using R scripts: Part2

opt

In the last post, I have explained the main concepts and usage of “Optimization” problems and “Linear Programming”. In this post, I am going to explain a simple example of solving an optimization problems from [1]. Imagin, we have a company that produce Product 1 and Product 2,  we want to maximize the profit of producing product 1 and 2.   and we want to know how much of each these products should we produce regarding our constraints. So what is constraints: We have some constraints on the number of working hours to produce each product. for producing 1 tonne of Product 1 we need 30 hours work, and for produce 2 we need 20 hours work. However, there is a limitation for working hours on each month that is 2700 hours. Also, there is some constraints on hours for producing each products as below: 1.5 hours for Product 1 and 10 hours for product 2. there is a limitation on hours that is 850. By producing product 1  we gain 20 USD and for product 2 we will gain 60USD. there is a minimum production for each time we run , that is the amounth of product 1 and 2 should be at least 95 tonnes. I am going to formulate like this: in below formula, we have product 1 as p1, and product 2 as p2.

 

we want to Maximize the profit of

20p1+60p2 (20USD of producing product 1 and 60 USD of producing Product 2)

Constraint: we have a limitation on machine hours for producing each product as below

1-5p1+10p2<850:the maximum time we able to spend to produce two products is 850. For producing one tonne of product 1 we need 5 hours and for product 2 we need 10 hours)

2-P1+P2>95. we have a minimum number of tonne that we should produce that is 95 .

3-30p1+20p2<2700. there is a limitation on the working hours  that should be spend to produce P1 and P2 that is 2700.

p1 and p2 should be greater than zero (we should produce both of them) so by gathering the whole constraints and objectives we have below formula:

MAX z = 20P1 + 60P2 s.t. : 30P1 + 20P2 < =2700 5P1 + 10P2 < =850 P1 + P2 >= 95 P1  >0; P2>  0

In the above code, we have a formula that we able to write R codes. there is a library in R studio name: “lpSolve” that helps us to solve and find out how much of produce 1 or 2 we should produce to maximize the profit.

library("lpSolve")
obj.fun<-c(20,60)

I installed the package”lpSolve”, then in above code, I just call it. I put the number related to the objective (20 USD profit for the production of each tonne of Product 1 and 30 USD profit for product 2) into a new variable called” obj.fun”

Next, I am going to create a matrix of the weight of p1 and p2. the first constraint has the weight of 5 and 1 for product 1 and 2. the second constraint has wieght 1 and 1 for each product, finally the last constraint has weight 30 and 20 for product 1 and 2, we put all these values in a vector as below. I am going to create a Matrix that  has two columns one for product 1 and another for product 2. so the “ncol” would be 2. The matrix will be saved into variable “constr”

constr<-matrix(c(5,10,1,1,30,20),ncol=2,byrow = TRUE)

Next, the value located on right and side (rhs) will be saved in the other variable name “rhs” as below

rhs<-c(2700,850,95)

Moreover, the sign for greater than or less than also should be stored in other variables as below:

const.dir<-c("<=",">=","<=")

finally, I used a function name “lp” to find the best amount of p1 and p2.

prod.sol<-lp("max",obj.fun,constr,const.dir, rhs,compute.sens = TRUE)

lp is a function that gets some input variables.

the first input is “max” that is mean we are going to find the maximum profit. the second input is the weight of objective function which is store in vector”obj.constr”. also for lp function we need constr weight, direction and rhs.

by running the above code, then by accessing the column”solution”

prod.sol$solution

we have below result

for product 1 we should produce about 20 tonne and for product 2 we should produce 75. also, the profit will be 20*20+60*75=4900

you can see the final profit by run the below code

prod.sol$objval

which shows 4900.

In the next post, I will show how to have this calculation in Power BI.

 

 

[1]http://wps.prenhall.com/wps/media/objects/2234/2288589/ModB.pdf

Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

Leave a Reply