Forum Discussion

Fadwa's avatar
Fadwa
Frequent Visitor
6 years ago
Solved

Convert excel model document (Goal seek) to Power BI

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,...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Fadwa
Frequent Visitor
6 years ago

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's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Fadwa ,

     

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

     

    Regards,

    Jimmy Tao

    • Fadwa's avatar
      Fadwa
      Frequent Visitor
      6 years ago

      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's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.