Dynamic Line Graph - Converting VBA

[Anonymous]

I have an excel workbook that utilizes a large dataset to plot performance relative to a benchmark. I am to the point where it makes sense to build this into a Power BI toolset as the input data is becoming quite large to manage. 

Importing the data for the actual performance is relatively easy, what I am struggling with is replicating the VBA that was written that dynamically creates the benchmark metric relative to a few different static parameters. 

Below is an example of one graph showing three items of analysis relative to the benchmark (purple line):

The purple line is graphed in excel based on some VBA code to calculate the shape of the curve from an inputs-table in the tool

The inputs are dynamically assigned based on a dropdown list of benchmark entries. In the example above, the benchmark is benchmark 'A' which prompts the inputs to dynamically read dedicated variables for the benchmark into the VBA code for the line graph.

Drop-down selection: _MB_UP_44_1.5S_SINGLE 

Input 1 -> 1.3

Input 2 -> 79.1%

and so on. 

Any help would be great. It is not a very complicated VBA script. I am just not sure if I can achieve this with what-if parameters and measures or if I need to utilize a Python visual.

[d_gosbell]

---

@Anonymous wrote:

Any help would be great. It is not a very complicated VBA script. I am just not sure if I can achieve this with what-if parameters and measures or if I need to utilize a Python visual.

---

I don't think we have enough information to answer this question. As far as I can see there are at least 2 questions here.

1) can the VBA code be converted to "native" Power BI code (ie. DAX or M / Power Query)?

Maybe, but we can't really answer this without seeing the VBA.

2) Can parameters for the benchmark be passed using "what-if" parameters?

Again I'm not really sure. If your end users manually type in numeric values for "input 1" and "input 2" then this approach of what-if parameters might make sense.

But you also seemed to imply that end users just select "Benchmark A" and this relates to a certain set of parameters for the benchmark algorithm. If this is the case it might make more sense to create a "Benchmarks" table which has the specified set parameters for each different benchmark.

[Anonymous]

@d_gosbell 

For the first part, the link below takes you to the vba module that is used to build the line graph from the selected inputs

https://github.com/rstover/DCA-Analysis/blob/master/Arps%20Equation

For part 2, 

I am thinking I may want it to be both. I'd like  the benchmark selection to each have a default input but allow the end user to be able to  "what-if" from those defaults based on the selection.