Don't know if this is possible. Here is what I need to do.
Looking at total giving by fiscal year, group donors by <10K, >10K to <100K, and greater than 100K. The problem is in looking at the total giving by these amounts. So, I have a donor who gave more than 100K, but in less than 10K increments to multiple campaigns. I can group by donation amount just fine, but this only works for donors who give one time for the amount I need. For donors who give more than once, I'm struggling to figure out how to group them into these custom categories. Is this possible to do?
Example: Donor A, 2021 contributions, 60K to campaign 1, 40K to campaign 2.
Donor B, 2021 contributions 120K to campaign 1
Donor C, 2021 contributions 15K to campaign 2
What I want is the donor count for 100K+ donors to be 2 and total giving to be 220K and donor count for 10K-100K to be 1 and total giving to be 15K. This would be in a pivot table so I can have fiscal year in the rows and see the trends of number of donors and donor amounts in these 3 custom groupings.
Hey Chris - gave this a shot, but am not getting anything in the return. If I'm understanding right, sum is on the outside, inside I group by constituent ID and only return the total if the amount is >=100K (for the first one), otherwise return null. Note: I tried returning 0 just in case the NULL caused a problem, but then I only get a value of 0 in all columns.
Here's my exact formula: sum ( [Constituent ID] , if ( [Total Amount] >=100000, [Total Amount] , NULL ) )
For my pivot table, I've got Campaign Fiscal Year as the Rows. Got any suggestions?