how to calculate the net profit i have cost price and sales price

[Hassan_Tanna]

how to calculate the net profit i have cost price and sales price in product table !  i have cost price in the product table only and i want make a chart or table to show me the net profit of total orders !

product tableproduct table

[Joseph_Seddik · Accepted solution]

According to your tables, what you need to do in order to calculate profit per order is to subtract the relevant products cost + shipping fees from the total value of the order that you name it "Total Cost". 

To do this you can do the following:

1. In your OrderDetails table add a new column "Product Cost". Its formula should be: 
   
   [Product ID].[Cost Price]
   
   
2. The total products cost for your order in Orders table will be: 
   
   SUM([Related OrderDetails][Product Cost])
   
   
3. The total shipping fees for your order in Orders table will be: 
   
   SUM([Related OrderDetails][shipping fees])
   
   
4. Thus, the total profit for your order will be: 
   
   [Total Cost] 
   - SUM([Related OrderDetails][Product Cost])
   - SUM([Related OrderDetails][shipping fees])
   
   

I noticed that you add the shipping fees to only one item per order in your OrderDetails table. This indicates that you are collecting a shipping fee per order not per item, which is the expected usual practice. Hence, it is better to remove the shipping fee column from the OrderDetails table and add it instead to the Orders table. In this case, the expression should be:

[Total Cost] - [shipping fees]
- SUM([Related OrderDetails][Product Cost])

Read these:

Dereference Expressions | AppSheet Help Center 
List Dereference | AppSheet Help Center 

[Joseph_Seddik · Accepted solution]

Order date?

and instead of the “…” you should complete the list of months 

[Hassan_Tanna]

@Joseph_Seddik hellooo ya boss could you help me please 🙂 

[Joseph_Seddik]

ازيك يا حسن 🙂

I remember your app, but can't figure out from your photos which table is which. Can you please show me the sheet's tables and make sure the sheet name on the bottom is visible. Thank you.

[Hassan_Tanna]

tables 

[Joseph_Seddik · Accepted solution]

According to your tables, what you need to do in order to calculate profit per order is to subtract the relevant products cost + shipping fees from the total value of the order that you name it "Total Cost". 

To do this you can do the following:

1. In your OrderDetails table add a new column "Product Cost". Its formula should be: 
   
   [Product ID].[Cost Price]
   
   
2. The total products cost for your order in Orders table will be: 
   
   SUM([Related OrderDetails][Product Cost])
   
   
3. The total shipping fees for your order in Orders table will be: 
   
   SUM([Related OrderDetails][shipping fees])
   
   
4. Thus, the total profit for your order will be: 
   
   [Total Cost] 
   - SUM([Related OrderDetails][Product Cost])
   - SUM([Related OrderDetails][shipping fees])
   
   

I noticed that you add the shipping fees to only one item per order in your OrderDetails table. This indicates that you are collecting a shipping fee per order not per item, which is the expected usual practice. Hence, it is better to remove the shipping fee column from the OrderDetails table and add it instead to the Orders table. In this case, the expression should be:

[Total Cost] - [shipping fees]
- SUM([Related OrderDetails][Product Cost])

Read these:

Dereference Expressions | AppSheet Help Center 
List Dereference | AppSheet Help Center 

[Hassan_Tanna]

حبيبي والله الف شكر 🙏 🙏🙏

You are the best its working as usual from you  ,  thank you so much 😊😍🙏

[Joseph_Seddik]

تسلم، بسيطة يا صديقي --<@

[Hassan_Tanna]

boss hello i would like ask about something firstly everything work great and i can now know the net profit and i removed the shipping fees from order details as you told me and i add it in orders table  but i have a problem with total cost , before was,  SUM ( SELECT (OrderDetails[Total], [Order Id] = [_THISROW].[Order Id])) , now i want add the shipping fees in the total cost , how its work !

[Hassan_Tanna]

@Joseph_Seddik i solved i used this expression and its work [shipping fees]
+ SUM([Related OrderDetails][total])

[Joseph_Seddik]

Right!

انت بقيت أستاذ 😀

[Hassan_Tanna]

دا انت الاستاذ والله بنتعلم منك 😍

[Hassan_Tanna]

@Joseph_Seddik  i would like to ask another question what if i want know  the profit by the month ! ( i dont have any row of month's or tables ) 

[Joseph_Seddik]

The easiest way is to add "Month" and "Year" columns. This will allow you to group by these columns in the view and display the sum automatically. 

[Hassan_Tanna]

ok add them in order table right ! and month columns make it enum and the monthes or make it taxt type and add the month  table and jan feb .. in the google sheet ! which better !

[Joseph_Seddik]

Enum with app formula:

INDEX({"Jan", "Feb", ... , "Nov", "Dec"}, MONTH([date]))

[Hassan_Tanna]

its give me this massage i dont have date column 

[Joseph_Seddik · Accepted solution]

Order date?

and instead of the “…” you should complete the list of months 

[Hassan_Tanna]

حبيبي  تسلم يا باشا  

done ya king its working now thank you so much 🙏

[Hassan_Tanna]

@Joseph_Seddik this massage come to me !

[Joseph_Seddik]

Enter the months one by one in order in the allowed values of the enum column. 

[Hassan_Tanna]

I can’t put anything in the month column ( invalid date)

 

[Joseph_Seddik]

Not here. Open the column in the app editor. In Type Details add the months as allowed values. 

I’m on mobile and can’t make you a screenshot. 

https://help.appsheet.com/en/articles/961539-drop-down-from-simple-list-of-values

[Hassan_Tanna]

I already added the months but nothing changed 

 

[Joseph_Seddik · Accepted solution]

The expression has to match the values. 
In the expression you have short names: Jan, Feb, etc. 
In the values you have long names: January, February, etc. 

Change either to match the other. 

[Hassan_Tanna]

You are a king 👑 of solutions 👍

[Hassan_Tanna]

@Joseph_Seddik  joooo ezyek question of day ya jo 😀 i don't have category in order datiles table but i have one in the products table i want add category in order details table to be specific when i make order show  for me only the products of this category only  , may its will be better if i add you in my app and take a look and give me all your comments  of the app