Internal datasource in app

[Liebmann_Attila]

Hi Community, 

I would like to do sort of a "lookup" in PowerApps. I would not like to have any external datasources as a list or an excel. 
What I thought would be nice is to have the data in an excel like format on a screen in the app where users can't go to and look up the data from there.  Is that possible? 

Detailed issue: 
I want to build sort of a calculator where one can calculate a payout based on a payout curve. 
Your achievement relates to a relevant payout curve and if you achieve X% you will receive the related percentage on the payout curve.

Example : 
The payout curve: 

Achievement	Payout%
89%	0%
90%	29%
91%	34%
92%	41%
93%	47%
94%	55%
95%	61%
96%	69%
97%	77%
98%	84%
99%	91%
100%	100%
101%	104%
102%	108%
103%	112%
104%	116%
105%	120%
106%	122%
107%	124%
108%	126%
109%	128%
110%	130%
111%	132%
112%	134%
113%	136%
114%	138%
115%	140%
116%	142%

Example1 : If you achieve 101% in your performance, you will receive 104% as your payout. 
Example2 : If you achieve 89% (or below) in your performance, you will receive 0% as your payout. 
Example3 : If you achieve 115% in your performance, you will receive 140% as your payout. 
Example4 : If you achieve 130% in your performance, you will receive 142% as your payout.
(because it is capped at 116% achievement)

if possible, I would not want to have the payout curve data in a separate file, that one needs to have access to in order to use the tool. Or that one could find or modify. I would like to have it in the app. 
It would be nice if one could write his/her "Achievement" in "textinput A" and the relevant "Payout%" would pop up
in "textinput B", based on the above curve. 

Could someone please help me in this? 
Thank you so much for all of your suggestions in advance!

[mmbr1606]

hey @Liebmann_Attila 

you can try this in the app onstart or visible of first screen:

ClearCollect(PayoutCurve,
    {Achievement: 89, PayoutPercentage: 0},
    {Achievement: 90, PayoutPercentage: 29},
    {Achievement: 91, PayoutPercentage: 34},
    {Achievement: 92, PayoutPercentage: 41},
    {Achievement: 93, PayoutPercentage: 47},
    {Achievement: 94, PayoutPercentage: 55},
    {Achievement: 95, PayoutPercentage: 61},
    {Achievement: 96, PayoutPercentage: 69},
    {Achievement: 97, PayoutPercentage: 77},
    {Achievement: 98, PayoutPercentage: 84},
    {Achievement: 99, PayoutPercentage: 91},
    {Achievement: 100, PayoutPercentage: 100},
    {Achievement: 101, PayoutPercentage: 104},
    {Achievement: 102, PayoutPercentage: 108},
    {Achievement: 103, PayoutPercentage: 112},
    {Achievement: 104, PayoutPercentage: 116},
    {Achievement: 105, PayoutPercentage: 120},
    {Achievement: 106, PayoutPercentage: 122},
    {Achievement: 107, PayoutPercentage: 124},
    {Achievement: 108, PayoutPercentage: 126},
    {Achievement: 109, PayoutPercentage: 128},
    {Achievement: 110, PayoutPercentage: 130},
    {Achievement: 111, PayoutPercentage: 132},
    {Achievement: 112, PayoutPercentage: 134},
    {Achievement: 113, PayoutPercentage: 136},
    {Achievement: 114, PayoutPercentage: 138},
    {Achievement: 115, PayoutPercentage: 140},
    {Achievement: 116, PayoutPercentage: 142}
);

textinput a will hold the value placed by the user.

textinout b try this in the default property:

If(
    Value(TextInputA.Text) < 89,
    0,
    LookUp(
        PayoutCurve,
        Achievement >= Value(TextInputA.Text),
        PayoutPercentage,
        LookUp(PayoutCurve, Achievement = 116, PayoutPercentage)
    )
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[Liebmann_Attila]

Hi mmbr1606 ,

thank you for your response.
If I understand correctly if I copy paste the first screenshot on "Onvisible" of my main screen, and use the second script you shared on the actual input field it should refer to the payout curve we entered in the "OnVisible" . 
I have modified the formula according to my naming's : 
but the lookup function seems to be falling off. 

I tried to play around with brackets, but and found that a bracket might be needed after the first "LookUp"

Unfortunately it still gives an error. 
The error is the following : 

" 
This error occurs when there are unexpected characters used in the Power Fx expression. It means that the expression does not follow the syntax rules of Power Fx and contains characters that are not recognized or used in an unexpected way. The specific characters causing the error are not mentioned in the given summary.
"

if I exclude the bracket on the end : 

It wont give an error but it will always show "0" no matter what the "ATTAINMENT_PROD_1" field states. 

[mmbr1606]

hey @Liebmann_Attila 

what does the app checker say on the red underlined text?

[Liebmann_Attila]

Hi mmbr1606, 

It is an unexpected character error. I tried to quote it above. 
If I remove the last bracket the error disappears but the formula shows 0 only. 

Could it be that it should look for 89% not only 89? 
As the achievement is in percentages as well.

[Liebmann_Attila]

@mmbr1606 , 

The "Attainment (or achievement) field is a calculated field already where I display a number in percentages.: 

The formula for it goes like : 

Text(

    (

        If(

        (IsBlank(Value(PRODUCT1_TARGET.Text))),0,

            (Round((Value(PRODUCT1_SALES.Text)/(Value(PRODUCT1_TARGET.Text))),2)))

    ) * 100

,"0%")

[mmbr1606]

try this approach

for the payout label:

If(
    Value(Label_Achievement.Text) < 89,
    "0%",
    Text(
        LookUp(
            PayoutCurve,
            Achievement >= Value(Label_Achievement.Text),
            PayoutPercentage,
            LookUp(PayoutCurve, Achievement = 116, PayoutPercentage)  // Caps at max achievement
        ),
        "0%"
    )
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[Liebmann_Attila]

@mmbr1606 , I have tried accordingly. 

Unfortunately it did not work. 
The following error was received.: 

[mmbr1606]

can u try this modification;

Text(
    If(
        Value(Label_Achievement.Text) < 89,
        0,
        If(
            Value(Label_Achievement.Text) > 116,
            LookUp(PayoutCurve, Achievement = 116, PayoutPercentage),
            LookUp(PayoutCurve, Achievement >= Value(Label_Achievement.Text), PayoutPercentage)
        )
    ),
    "0%"
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[Liebmann_Attila]

@mmbr1606 , 

the formula is functional (it does not give errors, but no matter the achievement (ATTAINMENT_PROD_1) 's value it shows as 0% . 

The copilot explanation for the formula is as follows: 
"This expression converts the value of ATTAINMENT_PROD_1 to text and applies a series of conditions and lookups to determine the appropriate payout percentage. If the value is less than 89, it returns 0%. If the value is greater than 116, it looks up the payout percentage from the PayoutCurve table where the Achievement is 116. If the value falls within the range of the Achievement values in the PayoutCurve table, it looks up the corresponding payout percentage. The final result is formatted as text with a "0%" suffix."