cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jake-hodgson
Frequent Visitor

Passing calculated fields to Excel

Hi,

 

I'm a bit of a PowerApps newbie so just getting to grips with things.

 

I'm looking a making a really simple profit & loss app for a small events company, one revenue field and a few costs fields to be sumed and then subtracted from the 'Revenue' field. 

 

Firstly I created a basic table in Excel for all the fields with the calculations for the total costs and profit field. I then created the app from the Excel sheet so it created the required screens and forms for editing and viewing the data. It didn't like the formulas I had put in so I've removed them from the Excel table. How do I make PowerApps add the calculations to the fields in the Excel workbook?

 

Thanks in Advance!

Jake

3 REPLIES 3
rc
Power Apps
Power Apps

Welcome to PowerApps, Jake!

 

You can use the functions in PowerApps to default values in your app form and then pass those values to columns in Excel. Her's a quick runthrough:

 

I've recreated an Excel table like you describe and stored on my OneDrive -- I've left the TotalCost and Profit columns blank without formulas:

2018-08-20_12h48_30.png

 

 

Then I generated an app from data, and pointed it at the same table.

 

On the form screen of this app, select the card for the Cost column. Then on the properties tab, switch to Advanced, and unlock the card.

 

Now you can change the Default property of the card to be a calculated expression, in this case summing the cost (and then later calculating profit), based on the updates a user would make when using the app:

 2018-08-20_13h00_19.png

 

 

 

The expression in this case:

Sum( EquipmentCost_DataCard2.Update, StaffCost_DataCard2.Update, TransportCost_DataCard2.Update )

 

When you submit the form, the items will write back to Excel.

 

The Budget Tracker sample app has some of these patterns in a bit more sophisticated detail that might be useful to check out.

 

Another option would be to use the Common Data Service instead of Excel, and take advantage of Calculated Fields there, which have the benefit of performing calculations like this server-side for any app using the data (instead of having to repeat the logic if multiple apps touch the same data source).

 

Good luck!

v-xida-msft
Community Support
Community Support

Hi @jake-hodgson,

 

Do you want to do some calculations on a few fields within your Excel table and then fill in the Total costs field and the Profit field using PowerApps?

 

I agree with @rc's thought almost. I have made a test on my side, please take a try with the following workaround:

 

The Excel table data structure:13.JPG

 

14.JPG

 

 

  • Generate an app from the Excel table.
  • Within the Edit form control of the Edit screen, Unlock the "TotalCost" Data card, Set the Default property of the TotalCost Data card contorl to following formula:

 

Value(EmployeeCost_DataCard2.Update)+Value(EquipmentCost_DataCard2.Update)

Note: The EmployeeCost_DataCard2 represents the EmployeeCost Data card, the EquipmentCost_DataCard2 represents the EquipmentCost Data card control within my Edit form.

 

       Unlock the "Profit" Data card control, set the Default property of the "Profit" Data card control to following formula:

Value(Revenue_DataCard2.Update)-Value(TotalCost_DataCard2.Update)

Note: The Revenue_DataCard2 represents the Revenue Data card, the TotalCost_DataCard2 represents the TotalCost Data card within my Edit form.

 

The GIF image as below:Test1.gif

 

 

 

 

Best regards,

Kris

 

 

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Guys,

 

Brilliant thanks for the advice and information, it was really helpful!

 

Thanks Jake 

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

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

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 (46,762)