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, 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!
Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

      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'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