Division not working?

Like (0) Share

Report

Posted on 8 Oct 2019 12:54:20 by drawser

107

So I have two tables (Sharepoint lists)

One uses a lookup from the other.

I have two galleries displaying them, basic set up is top gallery consists of Customers, lower gallery Customer orders.

What I'm trying to achieve is I want to do with AddColumns a calculation that gives me the percentage of items in the order delivered.

The idea with this to count the rows in Customer Order Based on the Customer ID, where status is "Levererad"(delivered), exclude Articles that contains "Behöver" divide those by the number of articles in the customer order that doesn't contain "Behöver" or that doesn't contain "Makulerad" in the field Status.

This is the item property in the gallery

AddColumns(
 Huvudorder,
 "PR",
 RoundDown(
 CountRows(
 Filter(
 Artikel_Order,
 Artikel_Order[@Status] = "Levererad" && Anställd.Id = Huvudorder[@ID] && (IsMatch(
 Artikel.Value,
 "Behöver",
 Contains
 ) = false)
 )
 ) / CountRows(
 Filter(
 Artikel_Order,
 Anställd.Id = Huvudorder[@ID] && (IsMatch(
 Artikel.Value,
 "Behöver",
 Contains
 ) = false) && (IsMatch(
 Status,
 "Makulerad",
 Contains
 ) = false)
 )
 ) * 100,
 0
 )
)

If I change the / to either a +,- or * it works. But the / just leaves me with a blank gallery.

If I skip the AddColumns Part and inject the same formula into a TextLabel in the gallery it works.

Any idea on why this just won't work?

Categories:

General Questions

All responses (3)

Answers (1)

mdevaney 29,987 Super User 2025 Season 1 on 08 Oct 2019 at 13:27:38

Like (0)

Report

Re: Division not working?

@drawser
No problem. It was instinctive to me after many years of preparing reports in Excel and getting the #DIV0 error. LOL.
drawser 107 on 08 Oct 2019 at 13:14:28

Like (0)

Report

Re: Division not working?

Yup, thanks alot!
That fixed the issue, I didn't even reflect over the ifError cause it worked in a textlabel.

Added it in there and now it works as intended.

Many thanks!
Verified answer

mdevaney 29,987 Super User 2025 Season 1 on 08 Oct 2019 at 13:00:21

Like (1)

Report
Re: Division not working?

@drawser
One possibility for error when dividing a number might be a "divide by zero" error. What happens if you encapusulate the calculation within an IFERROR function and return 0 for any errors?

AddColumns( Huvudorder, "PR", IfError( RoundDown( CountRows( Filter( Artikel_Order, Artikel_Order[@Status] = "Levererad" && Anställd.Id = Huvudorder[@ID] && (IsMatch( Artikel.Value, "Behöver", Contains ) = false) ) ) / CountRows( Filter( Artikel_Order, Anställd.Id = Huvudorder[@ID] && (IsMatch( Artikel.Value, "Behöver", Contains ) = false) && (IsMatch( Status, "Makulerad", Contains ) = false) ) ) * 100, 0 ), 0 ) )

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."