cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power Automate
Power Automate

Please help: Sum function results in Incorrect Calculation (off by 2)

Let me start with the scenario:

I have a sharepoint list (WOServices) with work order details and cost values. The list has 5 columns, with only 1 complex column (performed by/people column). I also have a Power App that is connected to it, which is submitting work order items and cost values to that list. Everything is working perfectly as far as list submission goes.

 

The WOServices list currently has 28 rows in it, and every row has been created today. However, in practice, there will be an average of 30 rows for each day, with the list itself growing to around 3,600 records in a year. We have a default view which is scoped to this weeks records - so the default view will never exceed 150 records. Tools: The default view in SharePoint, AND all historical entries not shown in it, will be connected to a Power BI Dashboard. On the other hand, all data entries will be done using Power Apps (tablet form).

 

Problem

I would like to include a read only text label in the header area of the form which sums all the orders for that day only. As a result, each time the form is opened, there is a daily tally for the day at the top in the header.

 

I'm using this function in the text label in the header:

Text(Sum(Filter(WOServices, IsToday(Created) = true), Cost), "$ #,###")

 

Only, the total comes out to be $185.00 in PowerApps, but in SharePoint (Default View Totals the Cost column) it adds up to $187.00 for that day.

 

So I exported the list to Excel to use sum formula to determine which was right; and SharePoint is right and Power Apps is wrong! This was a bit scary, can anyone shed any light on why this might be happening?

 

Thank you.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

So in scanning the list - I think I might have a "clue" (it's not worth much in resale value though-LOL). There is a line item of $2.00 that has a created time of 12:04 AM today which technically makes it the same day as all the rest. (Yes, I work all hours of the day and night - lol). However, I vaguely remember someone saying that Power Apps clocks are not literal but UTC or something like that (that they differ from SharePoint in some way). So perhaps that line item is not being understood as today.

 

Do you think that could be it? If so, that might not be an issue since I'm the only nut working at midnight.

View solution in original post

Highlighted

Pasted Cost Column (test values) in message below, with create times.

 

 

View solution in original post

11 REPLIES 11
Highlighted
Power Apps
Power Apps

It's hard to diagnose without having access to your data. When you look at the data in your list, is it obvious to you which row(s) are not factored into the sum? (adding up to $2.00). If so, is there anything special about those rows? Is the data in those rows typed properly? (You can probably tell whether that's the issue by forcing a type conversion: use Cost+0 instead of Cost in your Sum expression).

Radu Gruian [MSFT] ** PowerApps Staff
Highlighted

Yes, I have done a evaluation of the data and found no flaws (FYI: SharePoint column totals would be wrong too if the data formats were flawed.)

 

There are currently 28 rows in the list - and I can't tell where the "2" of the total is missing.

 

The Cost column is numeric and there are no null values.

Highlighted

Can you please paste the Cost column here? (all 28 values)

Thank you.

Radu Gruian [MSFT] ** PowerApps Staff
Highlighted

So in scanning the list - I think I might have a "clue" (it's not worth much in resale value though-LOL). There is a line item of $2.00 that has a created time of 12:04 AM today which technically makes it the same day as all the rest. (Yes, I work all hours of the day and night - lol). However, I vaguely remember someone saying that Power Apps clocks are not literal but UTC or something like that (that they differ from SharePoint in some way). So perhaps that line item is not being understood as today.

 

Do you think that could be it? If so, that might not be an issue since I'm the only nut working at midnight.

View solution in original post

Highlighted

Pasted Cost Column (test values) in message below, with create times.

 

 

View solution in original post

Highlighted

It's hard to tell without actually debugging on the real data, but I guess that is a possibility.

Radu Gruian [MSFT] ** PowerApps Staff
Highlighted

Ok, well - that's a nasty bug for anyone in manufacturing or retail that needs to track costs or other numbers by datee. But, I'll go with it for now! Thanks for offering to help!

Highlighted

Try this:


Sum(WOServices, Cost)

 

if that gives you the correct result, then yes -- it's an issue with the notion of "today". I will file a bug regardless. It would be helpful to have your exact data for repro (just those two columns would suffice).

 

Thanks.

 

Radu Gruian [MSFT] ** PowerApps Staff
Highlighted

Yes, you are correct: when I remove the IsToday(Created) filter the cost total is now correct.

P.S. To further validate, I deleted that row, and added another created just now - and again the total was correct even with the date filter. So obviously "12:09AM" was not read by PowerApps as today (whereas it was by the SharePoint view).

 

Here is the data:

 Cost, Created
0, 9/14/2016 2:31
0, 9/14/2016 2:29
0, 9/14/2016 2:30
20, 9/14/2016 2:23
0, 9/14/2016 2:31
30, 9/14/2016 2:26
11, 9/14/2016 2:19
0, 9/14/2016 2:29
2, 9/14/2016 2:35
3, 9/14/2016 2:20
0, 9/14/2016 2:21
20, 9/14/2016 2:23
20, 9/14/2016 2:26
0, 9/14/2016 1:14
13, 9/14/2016 2:11
10, 9/14/2016 2:35
0, 9/14/2016 2:30
0, 9/14/2016 3:16
0, 9/14/2016 3:18
0, 9/14/2016 3:18
27, 9/14/2016 2:22
26, 9/14/2016 1:45
2, 9/14/2016 0:09
3, 9/14/2016 2:35
0, 9/14/2016 3:17
0, 9/14/2016 3:17
0, 9/14/2016 3:18
0, 9/14/2016 3:16

 

Thank you for logging the bug!

Repro: Have the SharePoint List View Total the Cost Column
Create expression in Power Apps in text box:
Text(Sum(Filter(WOServices, IsToday(Created) = true), Cost), "$ #,###")

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (12,074)