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

(0) Share

Report

Posted on by audrieFCT

987

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (2)

Sort by

rgruian Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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).

Was this reply helpful? Yes No
audrieFCT 987 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
rgruian Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

audrieFCT 987 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

audrieFCT 987 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
rgruian Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
audrieFCT 987 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
rgruian Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
audrieFCT 987 on at

Like (0)

Report
Copy link

Link copied!

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), "$ #,###")

Was this reply helpful? Yes No
audrieFCT 987 on at

Like (0)

Report
Copy link

Link copied!

One more thing rgruian.

I put a text box on my screen to see what date and time it thinks it is, and sure enough, it was 1 hour earlier (which would make that 12:09 read as the previous date). (So it's not UTC, maybe it's server time)

*******Update******
Oddly enough when I closed and reopened the app, the time displayed correctly (now 13:06:34)....so maybe the text box doesn't update until you restart an instance of the app.

Was this reply helpful? Yes No