cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerapps2019
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 CarlosFigueira
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 CarlosFigueira
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
Community Support Team

Re: Formulation of gaussian distributino formulae

Hi @powerapps2019 ,

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

Also please check and see if the following video would help you create your custom connector:

https://www.youtube.com/watch?v=dBCS1nPsDiE

https://www.youtube.com/watch?v=cSzI093-xgk

 

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.