Newb - Questions for a Gross Profit Sales Calculator

I am making an app to calculate the GP (Gross Profit) in each sale for a Mattress Store. So that my sales team can quickly calculate the amount of profit in a sale.

I’m using the form format so users can choose a product from a series of dropdowns which eventually leads the user to an RRP (Recommended Retail Price).

Once the RRP has been discovered, I have a “Discount Method” Column which gives the user 3 choices (Percentage, $$ Amount, None)

I have a few of questions;
1/ How do I create dependant dropdowns so that if the user selects ‘Percentage’ then they can input only a percentage in the next field. Alternately if they select $$ Amount they can only enter a dollar amount in the next field?
2/ Once the user has entered the discount by either percentage or dollar amount, how do I get the app to return an answer?
3/ How do I set up the formulas that calculate the difference between the sale/discounted price & the cost of the sale (sales tax, Visa Card Fees, Finance Charges, cost of goods).

The final result I am looking for is that if the sales calculation returns a GP (Gross Profit) lower than 40% the numbers go red, between 41-44% they are orange and 45% and above the numbers go green.

@Andrew_Stock1
Following your [Discount Method] column, you need to have 3 additional columns:

  • [Discount Percent] - Percent type
  • [Discount Amount] - Price type
  • [Sales Price] - Price type
    and use a Show_if expression in those columns respectively as:
=[Discount Method]="Percentage"

=[Discount Method]="Amount"

and you can use below AppFormula for the [Sales Price] column

=SWITCH(
[Discount Method],
"Percentage", [RRP] * (1 - [Discount Percent]),
"Amount",[RRP] - [Discount Amount],
[RRP]
)

As you now have the net [Sales Price] value, you can calculate your GP with simple math.
For the formatting, you can go to UX > Format Rules tab and set the condition for each color range you require like:

[GP] <= 0.40 - *For red text*

AND([GP] >= 0.41, [GP] <= 0.44) - *For orange text*

[GP] >= 0.45 - *For green text*
2 Likes

Hi Levent,

Thank you, somehow I still don’t have the [Sale Price] returning an answer. But I do have the switch working. I have made a few adjustments to the column names as below;

I have changed the enum in [Discount Method] to “%,$,None” because the buttons are then small enough to sit on one line in the app.

Following the [Discount Method] column I have the following columns;

  • [Discount %] -Percent Type “Show if” = [Discount Method]="%"

  • [Discount ] -Price Type "Show if" =[Discount Method]=""

  • [Sale Price] -Price Type ÄppFormula

SWITCH(
[Discount Method],
“%”, [RRP] * (1 - [Discount %]),
"", [RRP] - [Discount ],
[RRP]
)

The result I am getting is as follows;
Lets say the selected product has a RRP of $4499

-if I choose [Discount Method], % and then enter in [Discount %] 10% the [Sale Price] does not change?

-if I choose [Discount Method], and then enter in [Discount ] $449 the [Sale Price] equals -$449

What am I doing wrong?

And is it possible for a user to choose [Discount Method] “None” and to have it return the same as [RRP] in the [Sale Price]? The reason I ask is that I have some calculations that will come into this after arriving at the sale price. Such as if the customer chooses to buy the bed on Hire Purchase.

Many Thanks

Hi Levent,

I got it working many thanks!

Hi @Andrew_Stock1 Would you care to share your working formulas thanks. It may help someone esle with a similar problem.

@Andrew_Stock1
Glad to hear that you made it working. Could you please mark my reply as solution? It might be beneficial for other community members as well, if and when they search the community with a solution tag. Thank you.