Optimization Problem in Power BI using R scripts: Part2



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.

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”

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

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

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

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”

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

which shows 4900.

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




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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">