cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SharepointByUse
Frequent Visitor

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 @SharepointByUse ,

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

2 REPLIES 2
yashag2255
Dual Super User II
Dual Super User II

@SharepointByUse 

 

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 @SharepointByUse ,

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (51,808)