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.
Hi David, I'm going to shorthand this but here's the idea using Multi-Pass Aggregation in the bucketed formulas to get the total contribution amount within a constituent, and then return that total giving only when it meets the desired criteria.
Create a value called ">=100K"
SUM ( [Constituent ID] , IF ( [Total Amount] >= 100000 , [Total Amount] , NULL ))
Create a value called ">10K to <100K"
SUM ( [Constituent ID] , IF ( [Total Amount] >= 10000 AND [Total Amount] < 100000 , [Total Amount] , NULL ))