Answered

Get SUM for filter comparing dropdown value and SP lookup column

(0) Share

Report

Posted on by xsomej33

Dear all,

I'm recently struggling with the following scenario:
I need to present a number in a label, which would be SUM of all rows from a SP list filtered as per a value of a dropdown.

I have the following formula:

Sum(Filter('SP List', 'Dropdown'.Selected.Result = 'Local Code'.Value), 'Amount in EUR')

The 'Amount in EUR' is a currency type column.

The 'Local Code' is a lookup type column.

The formula above doesn't throw any errors, but there is no result, it's just blank.

Any idea how to improve it?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

WarrenBelz 153,721 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @xsomej33 ,

Firstly try

Sum( Filter( AddColumns( 'SP List', "LocalCode", 'Local Code'.Value ), LocalCode = 'Dropdown'.Selected.Result ), 'Amount in EUR' )

What type of field is 'Local Code' ?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
xsomej33 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

Thanks - I can see some values being calculated now, but the sum is not correct - looks like it's not taking into account all the rows from the list. Could this be caused by a delegation limitation?

Local Code is a lookup type field.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,721 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @xsomej33 ,

Yes - Sum() is not Delegable. As well, Lookup fields are problematic in many ways and require the AddColumns() function (which is also not Delegable) to work with many things including Sum(). Other than setting your Delegation limit to 2,000, the only other way of achieving what you are after if you have more records is making a big collection (this blog of mine may assist).

Also if you used a Text column instead of a lookup (another blog you might read) , you would be able to sum the records if the filter returned less than 2,000 records.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
xsomej33 on at

Like (0)

Report
Copy link

Link copied!

Thanks @WarrenBelz - I have increased the limit to 2000 items and it's now calculated correctly.

Was this reply helpful? Yes No