We're working on a dashboard to quickly report our YTD income by campaign category, campaign, and fund (based on a manual Excel format we've used for years) and have run into the weirdest problem!
I've entered the goal amounts in the Funds (through Campaigns) so we can check amount to goal, but it's not always displaying properly.
This is what the goal amounts should be (displaying properly if I have only Fund as the row in the pivot table):
But this is what's displaying when I add campaign above fund in the rows:
(Let me assure you, our goal for Gala is NOT $32M...)
The only active filters are for the entire Dashboard: Campaign Fiscal Year (FY23) and Fund Control Group (because we're in a consortium)
Any ideas?? Thanks in advance!
Hello Emily,
You need to use a concept called multi-pass aggregation.
Example: Budget Amounts. Budget Amounts for Performances, Campaigns, Funds, etc. will show up multiple times and will inflate your totals.
Syntax:
SUM ( [Campaign ID], MAX([Budget Amount]) )
The above formula just says, Group the data by Campaign ID, take the MAX budget amount per Campaign ID, and then SUM the Budget Amounts.
The above formula can show as SUM ( [Campaign ID], ([Max Budget Amount]) ) which is the same as SUM ( [Campaign ID], MAX([Budget Amount]) ).
That worked! Hooray! In case anyone winds up coming back to this thread with the same issue, the final formula I used was
SUM ([Fund ID], MAX([Fund Goal Amount]))
Thank you, Neil!