Hello!!
I am attempting to recreate a beloved Development Progress report in Analytics (for a number of reasons). I am beyond stumped on something that feels like it should be really simple, so I am turning to all of you in hopes that someone knows what I'm doing wrong.
Here's a .dash file with the widget in it: DevelopmentProgressWidget.dash
I have a pivot table laying out my campaign categories, campaigns, and then each goal and current amount raised. Those items all look good and subtotal appropriately.
Then I have a value for Percent to Goal (Amount/Max Campaign Goal), which calculates perfectly on the individual line levels but does not subtotal correctly.
I think it's got to be the formula I'm using for that value, but I have tried several variations and I just can't figure something out that works at all the levels. Any thoughts?
I haven't been able to figure out subtotaling the percentage columns either. :(
The Percent to Goal will need to use the multi-pass function for the Goal Amount in its divisor. Because there are multiple campaigns being included in that single row, and because we can't just change our sub total type to account for this being an overall percentage.
Part 1 of Take Your Tessitura Analytics Skills to the Next Level covers this at 6:33.
SUM( [Amount] ) / SUM( [Campaign ID] , Max( [Campaign Goal Amount] ) )
Thank you, Chris. This is exactly what I needed - I knew it existed!
Maery Simmons (Past Member),
When you get this working in the way you would like. Would you be willing to share the working .dash file with the community?