Greetings from the Dallas Zoo!
I am working on a dashboard for our Operations Officer. I am stumped with how to best include budgeted amounts to measure our performance against. Here is where I am now:
How do I get Analytics to return the budget amount only once, instead of once for each resulting row? As as stop-gap, I've set the formula to return only the max budget amount-- but this only works when we are looking at each month individually.
I have attached my .dash file if anyone is interested. Here's a screenshot of the area causing me the trouble:
3542.DRAFT_COODashboard1.dash
Thanks!
Shannon
Here is a link to an example from a video.
What is a Multi-pass Aggregation Formula?
https://www.tessituranetwork.com/en/Items/Videos/Training/Analytics/taffy-october-2019
9 minutes 45 seconds
Formula for the Secondary Value (an example of multi-pass aggregation):
SUM([Campaign ID]), MAX([Campaign Goal Amount]))-[Total Amount]
Syntax: Aggregation([Grouping Field], MAX([Value]))
In your case, something like:
SUM
(
[Performance ID],
MAX([Budget Amount])
)
To get it to work over multiple months you would probably have to add additional grouping fields to the left of the MAX part.
SUM([Performance ID], [Performance Date Filtered to Months], MAX([Budget Amount]))
Neil Cole said:SUM([Campaign ID]), MAX([Campaign Goal Amount]))-[Total Amount]
I noticed an incorrect right parenthesis in my example from the video. It should've been:
SUM([Campaign ID], MAX([Campaign Goal Amount)) - [Total Amount]
You may need to add Filtering fields so it knows which specific performances to take the MAX budget amount (Filtered Values). From your other widgets, I can see you already know how to do Filtered Values.