cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sum/subtract Sharepoint

Hello,

 

On SharePoint I have two columns "Spent" and "Amount" and I would like to be able to do subtractions on a "Total" column but I can't get subtractions to work on the "Default" PowerApps box.

 

Is it possible ?

If so what is the procedure to follow please

 

Spent = 34

Ammount = 11

34 - 11 = 23

TOTAL column = 23.

 

Thanks you advance, GL. 

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

Hi @Anonymous ,

What is the column type of your "Spent" and "Amount" column? Are they both Nmuber column?

Where do you want to display this "Total" column?

I assume your "Spent" and "Amount" column are Number column, and you want to display this “Total” column inside a DataTable. You can do the calculation inside PowerApps. I will give you the detailed steps about how to achieve this:

1. Create a new list called “subtract” with two Number column named “Spent” and “Amount”:

v-albai-msft_0-1611047581852.png

2. Go to your app and add a DataTable, set the Items property of this DataTable as below(remember to replace your own SP list name):

 

AddColumns(subtract,"Total",Spent-Amount)​

 

v-albai-msft_1-1611047581857.png

3. Go to the Properties pane of this DataTable, click “Edit fields” to add the new column added in step 2.

v-albai-msft_2-1611047581860.png

4. This time, check the results in your Data table:

v-albai-msft_3-1611047581861.png

If you create a new list item in SP list, and refresh your data source inside the app, you will see the new added record in the DataTable:

v-albai-msft_4-1611047581861.png

 

v-albai-msft_5-1611047581862.png

Note:

  1. AddColumns() function don't modify the original SP list. Instead, it takes that table as an argument and return a new table with a transform applied. So you cannot see the new added columns in your SP list.
  2. You can also use this formula in a Gallery if you want to display records in a gallery.

Best Regards,

Allen

View solution in original post

4 REPLIES 4

@Anonymous 

 

One option is to create a calculated column in SharePoint if you want to store the value. If you want to display the value in App, then you can use the below expression:

 

Value(DataCardValue1.Text) - Value(DataCardValue2.Text)

Here, you need to replace DataCardValue1 with the control name for Spent and DataCardValue2 with the control name for Amount.

 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

v-albai-msft
Community Support
Community Support

Hi @Anonymous ,

What is the column type of your "Spent" and "Amount" column? Are they both Nmuber column?

Where do you want to display this "Total" column?

I assume your "Spent" and "Amount" column are Number column, and you want to display this “Total” column inside a DataTable. You can do the calculation inside PowerApps. I will give you the detailed steps about how to achieve this:

1. Create a new list called “subtract” with two Number column named “Spent” and “Amount”:

v-albai-msft_0-1611047581852.png

2. Go to your app and add a DataTable, set the Items property of this DataTable as below(remember to replace your own SP list name):

 

AddColumns(subtract,"Total",Spent-Amount)​

 

v-albai-msft_1-1611047581857.png

3. Go to the Properties pane of this DataTable, click “Edit fields” to add the new column added in step 2.

v-albai-msft_2-1611047581860.png

4. This time, check the results in your Data table:

v-albai-msft_3-1611047581861.png

If you create a new list item in SP list, and refresh your data source inside the app, you will see the new added record in the DataTable:

v-albai-msft_4-1611047581861.png

 

v-albai-msft_5-1611047581862.png

Note:

  1. AddColumns() function don't modify the original SP list. Instead, it takes that table as an argument and return a new table with a transform applied. So you cannot see the new added columns in your SP list.
  2. You can also use this formula in a Gallery if you want to display records in a gallery.

Best Regards,

Allen

what about when it's more than two values? how do you subtract those

JalalGosavi
New Member

I have the same logic app. i am creating the apps for inventory and maintenance job . now i am facing the issue with total qty ,

That is calculated value column. formula is  Total qty = order qty + in stock qty - consumable qty . Also IN stock  qty is calculated value = order qty + Total Qty  .  I am getting fine result for first entry . But when i am trying to get 2nd time it showing wrong value . can you anyone suggest here .

E.g. 1st entry -->order qty -10

In stock qty - 5

Consumable - 4

total - 11

 

 

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,058)