DAX to Sum a series (Solve for Y)

[Chas2002]

Hi PwrBI Users,

Got a tricky one here. I need to Solve for Y by Summing the Series: A to the [POWER of i] divided by i to the [FACT of i].

　

And I need to do this on a per row basis, meaning not have SUMX calculate the column then return the value but calculate the row and return the value...

A = 10

i = 0 through 10 (the itteration)

Final SUM or SUMX Results should be:

The Power of A = 10000000000

The Factorial of i = 3628800

The SUM of itteration = 12842.3

I think this would be straight forward enough but I couldn't find a way to itterate through the series of i?

Thanks,

Chas2002

See attached image:

[Anonymous]

Hi @Chas2002 - I am also struggling to implement erlang c formula in pbi, it will be great if you can help in case you

have cracked the same

[v-yulgu-msft · Accepted solution]

Hi @Chas2002,

It would be much easier to calculate above values via DAX than M query.

Suppose the values of i is already a known field in dataset.

Then, please create calculated columns with below formulas.

i! =
IF (
    Test1[i] = 0,
    1,
    CALCULATE (
        PRODUCTX ( Test1, Test1[i] ),
        FILTER ( Test1, Test1[i] <= EARLIER ( Test1[i] ) && Test1[i] >= 1 )
    )
)

A^i = 10^Test1[i]

A^i/i! = Test1[A^i]/Test1[i!]

sum A^i/i! =
CALCULATE (
    SUM ( Test1[A^i/i!] ),
    FILTER ( Test1, Test1[i] <= EARLIER ( Test1[i] ) )
)

The default data type returned by Product function is decimal number, please change it to Whole number for [i!] field.

Best regards,

Yuliana Gu

[Greg_Deckler]

Can you post some sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, I am concerned about this one because you mention iterating over i. That's likely to cause a circular reference in DAX, which is bad. DAX no like. See my struggles with it here:

https://community.powerbi.com/t5/Community-Blog/Runge-Kutta-and-the-Limits-of-DAX/ba-p/357501

That being said, I eventually came up with this Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Runge-Kutta/m-p/411280

Now, you will probably have far more luck in M (Power Query) because you can use recursion. See my series on "Fun with Graphing in Power BI" for more on using recursion in M:

https://community.powerbi.com/t5/Community-Blog/Fun-with-Graphing-in-Power-BI-Part-5-by-5/ba-p/40772...

The ultimate recursion method is post 5 (above link) but links to the other 4 articles are at the bottom of each article.

[Chas2002]

Hi Greg,

Thanks for the links.  After visiting each, I think M (Power Query) looks like my best option.

I did a quick test in vbs to wrap my head around how it would look in M:

' SUM of Y Series
For i=0 to (n - 1)
	If i = 0 Then
	aFactorial = 1
	iFactorial = 1
	aFactorialpw = 1
	y = Y + (aFactorial / iFactorial)
	End If

	If i = 1 Then
	aFactorial = 10
	iFactorial = 1
	aFactorialpw = 10
	y = Y + (aFactorial / iFactorial)
	End If

	If i > 1 Then
	aFactorial = A^i[
	iFactorial = iFactorial * i
	aFactorialpw = (A^i) / iFactorial
	y = Y + (aFactorial / iFactorial)
	aFactorialpw = Round(aFactorialpw, 2)
	y = Round(y, 2)
	End If
	
Next

The above vbs gives the correct answer.

So my follow up question:  How can I have M Query use the value from table.field?  The 'n' at the very top of the code is different on each row -- that is what I need to run the calculation against and return it to the table as a new column/filed or (in some way) relate it to the table.

Thanks,

Chas

[Greg_Deckler]

So, n would be a parameter that you would pass into your recursive function. So you would call your function with something like:

Step = fnMyFunction([n])

If "n" were a column name. fnMyFunction would be your function name. You're going to have to think a little differently though since it is recursion and not a for loop, but it shouldn't be too bad. You would return "y" from your function each time so that would bubble back up eventually to the right value. Haven't sat down with your code below to recursify it, The only iterative value that I am seeing in the code is iFactorial so you would have to pass that into your recursive function each time.

[Chas2002]

Thanks Greg,

I'm reading up on M and recursion now.  I understand most of it except the Step(s) -- to me it reads that I should have a step for every possible solution of the series but that doesn't make sense.  🙂

[Anonymous]

Hi did you end up Figuring out how to solve this because I am running into the exact same problem