HELP WITH DAX: Simulate Excel What-If-Analysis Goal Seek

[astojanac]

Hello everyone, 

I need help with one DAX. Actually, I need something that is in excel called 'What-If-Analysis/Goal Seek' .

I would try to explain problem: 

I have two known components - example stock (I will name it A) and sales (I will name it B), at the moment. 

And I have ratio between them as end of period goal (I will name it GOAL).

My unknown component, the one I need help with, is forecast sale by the end of the period (I will name it X). 

A / B = CURRENT RATIO

(A-X) / (B+X) = GOAL 

X = ? 

If you think you can help me, you can ilustration in excel:

Excel example Goal Seek 

Thanks in advance, 
Alex

[v-kelly-msft · Accepted solution]

Hi @astojanac ,

1.You can make current ratio as a measure: 

measure=Devide(MAX(A),MAX(B))

2.Then convert your X expression as below:

x=Devide((A-Goal*B),(Goal+1))

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[astojanac]

Hi @v-kelly-msft T,

Thank you for your answer and thank you so much for your help. This is the formula I've been looking for. 

Kind regard, 
Alex

[amitchandak]

@astojanac 

In Powerbi, You have what-if parameter, see if it can help

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

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[astojanac]

Hello @amitchandak ,

Thank you for your answer.
I know for What-if parameter, but I can't find the way to use it in my case when I have unknown value in two places at the same time.