Help with Dax measure

[Mutts26]

Hi all, 

I have a formula from Excel that works, but we'd like to recreate this in Power Bi, however I'm having an issue getting the same results from my Dax measure as I get from my excel formula. I'm not receiving any errors from the calculation, it's just returning 0.0000. The idea is to add this measure into a table that shows the value as a monthly average. Any help is greatly appreciated. 

The excel formula is  =1/(10^((0.09018+2729.92/(C21+273.16))-AVERAGE(D16:D20))+1)*O21. 

The dax measure I've created is (***** was substituted for table names) 

=

CALCULATE (

    1

        / (

            10

                ^ (

                    (

                        0.09018

                            + 2729.92

                                / ( AVERAGE (*****[Temp] ) + 273.16 )

                    )

                )

                - AVERAGE (*****[pH] ) + 1

        )

        * AVERAGE (*****[Ammonia] )

)

[Mutts26]

Hi Greg, 

Sorry, I wasn't sure if that would make sense or not. Let me try sharing some more data. So from the excel calculation above, C21 is the average monthly Temp value from our excel file, D16:D20 is our monthly average pH value and O21 is our monthly average Ammonia value. 

Basically I'm just trying to recreate that excel formula from above in Power Bi, but can't seem to replicate the same values in power bi as I'm getting in excel. 

An example is =1/(10^((0.09018+2729.92/(13.1+273.16))-AVERAGE(7.062))+1)*0.37=0.0010  this was our excel calculation for the month of January, (bold values represent the cells as explained above. When I use the dax measure from the previous post in Power Bi, it returns 0.0000, which it's doing for all calculations, so I have gone astray with my calculation somewhere, I just need help figuring out where my Dax formula is wrong. 

I hope this makes a bit more sense. 

[Rupak_bi]

Hi,

The formula should work. please share the table with some sample data. will try to get the expected result in a measure.

[Mutts26]

Hi Rupak, thanks for responding. See below for a snippet of the data with the expected results for the Un-ionized ammonia column, using that Dax measure. Thanks, much appreciated. 

Date	Average Temp	Average pH	Average Ammonia	Expected Un-Ionized Ammonia
January, 2023	13.1	7.06	0.37	0.0010
February, 2023	12.8	7.63	0.18	0.0017
March, 2023	13.5	7.16	0.48	0.0017
April, 2023	14.2	7.8	0.17	0.0027
May, 2023	18.8	7.3	0.72	0.0050

[Dangar332]

Hi, @Mutts26 

try below code

result = 
1/(
    10^(

        (

            0.09018+2729.92/(AVERAGE('Table'[Average Temp])+273.16)
        )
        -AVERAGE('Table'[Average pH])
    )+1
)
*AVERAGE('Table'[Average Ammonia])

[Greg_Deckler]

@Mutts26 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.