cancel
Showing results for
Did you mean:
Level: Powered On

## Formulation of gaussian distributino formulae

Hello

I want to introduce a calculation in a form of a PowerApps application. Since PowerApps does not allow the conection to Excel tables with formulae, I have to code the formula.

For the Normal Distribution density there is a closed formula, which is easy to implement. But I have not been ale to code the Normal Cumulative distibution and its inverse. For an easier understanding, I would like to code the following Excel formulae:  DISTR.NORM.ESTAND.N(0.999;0) and DISTR.NORM.INV(0.999;0;1).

If it is not possible to code this, it is ok if you know to which data source shoould I connect where the formulas can be coded. As said, Excel is not an option.

Thank you very much for all your suggestions.

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff

## Re: Formulation of gaussian distributino formulae

There is no function in PowerApps that will give you the normal distribution values (either the CDF or the inverse). I suggest you to create a feature request in the PowerApps Ideas board for this scenario.

Currently there are a couple of ways you can accomplish this:

• You can create an API that provides these functions, and custom connector that exposes it to PowerApps. Once that is done, your app can use that API to calculate what you need.
• Use a lookup table for most of the values, which should give you an approximation of the actual results, then use a LookUp expression to retrieve the values.

For example, if you define these collections in the OnStart of your app:

```ClearCollect(
Normal;
{ x: -5; cdfNormal: 2,86651571879194E-07 }; { x: -4,99; cdfNormal: 3,01896462520848E-07 }; { x: -4,98; cdfNormal: 3,17921366185281E-07 }; { x: -4,97; cdfNormal: 3,34764508273617E-07 };
...
{ x: -0,04; cdfNormal: 0,484046563147169 }; { x: -0,03; cdfNormal: 0,488033526585887 }; { x: -0,02; cdfNormal: 0,492021686283098 }; { x: -0,01; cdfNormal: 0,496010643685368 };
{ x: 0; cdfNormal: 0,5 }; { x: 0,01; cdfNormal: 0,503989356314632 }; { x: 0,02; cdfNormal: 0,507978313716902 }; { x: 0,03; cdfNormal: 0,511966473414113 };
{ x: 0,04; cdfNormal: 0,515953436852831 }; { x: 0,05; cdfNormal: 0,519938805838372 }; { x: 0,06; cdfNormal: 0,523922182654107 }; { x: 0,07; cdfNormal: 0,527903170180521 };
...
{ x: 4,96; cdfNormal: 0,999999647534102 }; { x: 4,97; cdfNormal: 0,999999665235492 }; { x: 4,98; cdfNormal: 0,999999682078634 }; { x: 4,99; cdfNormal: 0,999999698103538 };
{ x: 5; cdfNormal: 0,999999713348428 });;
ClearCollect(
InvNormal;
{ p: 0,001; invNormal: -3,09023230616781 }; { p: 0,002; invNormal: -2,87816173909548 }; { p: 0,003; invNormal: -2,74778138544499 }; { p: 0,004; invNormal: -2,6520698079022 };
{ p: 0,005; invNormal: -2,5758293035489 }; { p: 0,006; invNormal: -2,51214432793046 }; { p: 0,007; invNormal: -2,45726339020544 }; { p: 0,008; invNormal: -2,40891554581546 };
...
{ p: 0,497; invNormal: -0,00751995569854052 }; { p: 0,498; invNormal: -0,00501327754892666 }; { p: 0,499; invNormal: -0,00250663089957177 }; { p: 0,5; invNormal: 0 };
{ p: 0,501; invNormal: 0,00250663089957177 }; { p: 0,502; invNormal: 0,00501327754892666 }; { p: 0,503; invNormal: 0,00751995569854052 }; { p: 0,504; invNormal: 0,0100266811002748 };
...
{ p: 0,993; invNormal: 2,45726339020544 }; { p: 0,994; invNormal: 2,51214432793046 }; { p: 0,995; invNormal: 2,5758293035489 }; { p: 0,996; invNormal: 2,6520698079022 };
{ p: 0,997; invNormal: 2,74778138544499 }; { p: 0,998; invNormal: 2,87816173909548 }; { p: 0,999; invNormal: 3,09023230616781 })```

Then you can use an expression like this

`LookUp(Normal; x = Round(Value(TextInput2.Text; "es-ES"); 2); cdfNormal)`

To retrieve the corresponding CDF value to what the user entered in the text input.

The attached app shows this expression for your scenario. To open it, save it locally, then go to https://create.powerapps.com, select Open (Abrir) -> Browse (Examinar archivos), and find the file that you saved.

Hope this helps!

2 REPLIES 2
PowerApps Staff

## Re: Formulation of gaussian distributino formulae

There is no function in PowerApps that will give you the normal distribution values (either the CDF or the inverse). I suggest you to create a feature request in the PowerApps Ideas board for this scenario.

Currently there are a couple of ways you can accomplish this:

• You can create an API that provides these functions, and custom connector that exposes it to PowerApps. Once that is done, your app can use that API to calculate what you need.
• Use a lookup table for most of the values, which should give you an approximation of the actual results, then use a LookUp expression to retrieve the values.

For example, if you define these collections in the OnStart of your app:

```ClearCollect(
Normal;
{ x: -5; cdfNormal: 2,86651571879194E-07 }; { x: -4,99; cdfNormal: 3,01896462520848E-07 }; { x: -4,98; cdfNormal: 3,17921366185281E-07 }; { x: -4,97; cdfNormal: 3,34764508273617E-07 };
...
{ x: -0,04; cdfNormal: 0,484046563147169 }; { x: -0,03; cdfNormal: 0,488033526585887 }; { x: -0,02; cdfNormal: 0,492021686283098 }; { x: -0,01; cdfNormal: 0,496010643685368 };
{ x: 0; cdfNormal: 0,5 }; { x: 0,01; cdfNormal: 0,503989356314632 }; { x: 0,02; cdfNormal: 0,507978313716902 }; { x: 0,03; cdfNormal: 0,511966473414113 };
{ x: 0,04; cdfNormal: 0,515953436852831 }; { x: 0,05; cdfNormal: 0,519938805838372 }; { x: 0,06; cdfNormal: 0,523922182654107 }; { x: 0,07; cdfNormal: 0,527903170180521 };
...
{ x: 4,96; cdfNormal: 0,999999647534102 }; { x: 4,97; cdfNormal: 0,999999665235492 }; { x: 4,98; cdfNormal: 0,999999682078634 }; { x: 4,99; cdfNormal: 0,999999698103538 };
{ x: 5; cdfNormal: 0,999999713348428 });;
ClearCollect(
InvNormal;
{ p: 0,001; invNormal: -3,09023230616781 }; { p: 0,002; invNormal: -2,87816173909548 }; { p: 0,003; invNormal: -2,74778138544499 }; { p: 0,004; invNormal: -2,6520698079022 };
{ p: 0,005; invNormal: -2,5758293035489 }; { p: 0,006; invNormal: -2,51214432793046 }; { p: 0,007; invNormal: -2,45726339020544 }; { p: 0,008; invNormal: -2,40891554581546 };
...
{ p: 0,497; invNormal: -0,00751995569854052 }; { p: 0,498; invNormal: -0,00501327754892666 }; { p: 0,499; invNormal: -0,00250663089957177 }; { p: 0,5; invNormal: 0 };
{ p: 0,501; invNormal: 0,00250663089957177 }; { p: 0,502; invNormal: 0,00501327754892666 }; { p: 0,503; invNormal: 0,00751995569854052 }; { p: 0,504; invNormal: 0,0100266811002748 };
...
{ p: 0,993; invNormal: 2,45726339020544 }; { p: 0,994; invNormal: 2,51214432793046 }; { p: 0,995; invNormal: 2,5758293035489 }; { p: 0,996; invNormal: 2,6520698079022 };
{ p: 0,997; invNormal: 2,74778138544499 }; { p: 0,998; invNormal: 2,87816173909548 }; { p: 0,999; invNormal: 3,09023230616781 })```

Then you can use an expression like this

`LookUp(Normal; x = Round(Value(TextInput2.Text; "es-ES"); 2); cdfNormal)`

To retrieve the corresponding CDF value to what the user entered in the text input.

The attached app shows this expression for your scenario. To open it, save it locally, then go to https://create.powerapps.com, select Open (Abrir) -> Browse (Examinar archivos), and find the file that you saved.

Hope this helps!

Community Support Team

## Re: Formulation of gaussian distributino formulae

Currently, within PowerApps, there is no function supported to calculate the Normal Cumulative distibution and its inverse.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

In addition, based on what I have known, there is no other data source supported to code the Normal Cumulative distibution and its inverse.

As an alternative solution, I think the Custom connector in PowerApps could achieve your needs. Firstly, you need to create a REST API on your side using programming language e.g. C#, within this API, you need to define the action/function to handle the Normal Cumulative distibution and its inverse.

After that, within your PowerApps, you could create a custom connector based on above REST API, and then create a connection to this custom connector from your app, then you could use the "Normal Cumulative distibution and its inverse" handle action defined in your API within your app.

More details about creating a custom connector, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/register-custom-api

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

#### Power Platform World Tour

Coming to a city near you

#### PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

#### Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

#### Power Platform Summit North America

Register by September 5 to save \$200

Top Kudoed Authors
Users Online
Currently online: 229 members 5,163 guests
Recent signins: