Integrate Excel Solver with Power BI

[cleadholm]

I am working on creating an optimization scenario for a Power BI Dashboard.

The scenario is intended to maximize gross margin return on investment (GMROI) based on a promotional order quantity. The inputs for the scenario are a customer's yearly sales volume, customer's on-hand inventory, customer's sell price, promotional purchase price, and our standard (non-promo) purchase price. So 5 inputs in total.
 
I was able to create a model in Excel user Solver, and it correctly adjusts the suggested promotional order quantity to maximize the customer's GMROI. However, I have not found a way to bridge a similar optimizaiton scenario / what-if analysis into Power BI.

Can Solver be integrated into Power BI, or do other solutions currently exist? 

Thanks for the help!

[cleadholm]

Thank you for the response @Ritaf1983 

I watched the tutorial, and the "what-if" parameters are helpful. However, I don't see anything being optimized in the example that was reviewed. In the example, I would essentially be looking to optimize the "Usage Parameter" so that we find the maximum number of "breach days". The dashboard shows the breach status by day based on the Usage that you set, but you have to adjust the usage to see the corresponding number of breach days. I want to see the optimal (maximum) GMROI without having to test multiple values (Order Quantity).

I hope this helps!

[Ritaf1983 · Accepted solution]

Hi @cleadholm 
You have the option to create "what if " scenarios too by using parameters.
Please take a look at the linked tutorial:

https://www.youtube.com/watch?v=HUhfz7dzfpA

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly