cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
audrieg
Level 10

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
audrieg
Level 10

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

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.

audrieg
Level 10

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

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

 

 

11 REPLIES 11
PowerApps Staff rgruian
PowerApps Staff

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

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
audrieg
Level 10

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

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.

PowerApps Staff rgruian
PowerApps Staff

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

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

Thank you.

Radu Gruian [MSFT] ** PowerApps Staff
audrieg
Level 10

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

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.

audrieg
Level 10

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

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

 

 

PowerApps Staff rgruian
PowerApps Staff

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

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

Radu Gruian [MSFT] ** PowerApps Staff
audrieg
Level 10

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

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!

PowerApps Staff rgruian
PowerApps Staff

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

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
audrieg
Level 10

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

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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 362 members 5,243 guests
Please welcome our newest community members: