Convert excel model document (Goal seek) to Power BI

[Fadwa]

Hello everyone,

I converted a whole excel model document to Power bi by translating every fx function into a new measure by DAX

And now after I got the Goal Seek function equations and its idea, I am struggling with converting it to Power BI because of the dependency of the equations on each other, like:

Vu = The value that we need to figure out (Have no equation or value)

Left-hand equation = include Vu

Right-hand equation = also include Vu

The goal = Left-hand equation - Right-hand equation = 0

Which means that I need both equations to be equaled by balanced them with the Vu value (L=R)

I hope I explained it in a clean way that you could get it

Thank You!

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output.

Appreciate your Kudos.

[Fadwa]

I don't know how to explain it because it is a privet data

But the idea is I have a dashboard build in an excel macro model (Dashboard, model, controls... sheets)

So I kinda build the dashboard again to the power bi but with DAX 

Like if I want to represent an average of any number that the user choose on power bi dashboard, I've to make:

• A list of minimum numbers in a separate excel sheet: MIN
• A list of maximum numbers in a separate excel sheet: MAX
• And a new measure called AVR for example and it will be: AVR = (SUM('MIN'[min])+SUM('MAX'[max]))/2

To be like this: https://drive.google.com/file/d/1_XKEQk_22yaCDbufX2cgGnPxdbaXR_zA/view?usp=sharing 

And go on for the rest of the equations

The problem is one of the cells on Excel (named Vu) give me a number so I built many equations on that number, but then I figure out that is not a value of a stable number, its a result of changing range on goal seek function:

Range("DIFF").GoalSeek Goal:=0, ChangingCell:=Range("VU")

which I mentioned before

[v-yuta-msft]

@Fadwa ,

I'm confused on your description, could you please share some sample data and clarify the logic of your requirement using some expressions?

Regards,

Jimmy Tao

[Fadwa]

I need to convert this VBA macro code to power bi

Sub Calculate()
'
' Calculate Macro
'

'
    Sheets("Model").Select
    Range("DIFF").Select
        For i = 1 To 10
            Application.CutCopyMode = False
            Application.CutCopyMode = False
            Application.CutCopyMode = False
            Range("DIFF").GoalSeek Goal:=0, ChangingCell:=Range("VU")
            Sheets("Mortgage").Select
            Application.CutCopyMode = False
            Application.CutCopyMode = False
            Application.CutCopyMode = False
            Range("DIFFMORTG").GoalSeek Goal:=0, ChangingCell:=Range("MORTG")
        Next
        Sheets("Dashboard").Select
        Range("Start").Select
End Sub

[v-yuta-msft]

@Fadwa ,

Could you share the sample data and show the expected result so that I can help you?

Regards,

Jimmy Tao

[Fadwa]

Hi again!

Thank you everyone! I really appreciate your efforts

I couldn't upload any picture before so I may have failed in explaining my idea + English is not my first language

So I simplify it to level 0 

for example here is the excel file and my idea of goal seek (L-R=0) which means L = R 

(R/L equations is much complicated on my excel macro model - its more like a study)

Here is the macro - VBA of the goal seek function

And here is the way I converted it into power bi - DAX

I want to make sure that whatever I put into L/R equations its always gonna be equals by using v value to balance them

Is there any function that could change the Goal value to be 0 by changing the v value automatically?

There is no data, it's only an equations 

[v-yuta-msft · Accepted solution]

@Fadwa ,

What-if parameter may meet your requirement, please refer to doc below:

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Community Support Team _ Jimmy Tao

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