How about using count distinct to determine the number of different periods you work with and then dividing the sum of all budgets by the result to get to the true budget amount. I attach an example, and hoe it's helphul a little bit.
Book2.twbx.zip 13.2 KB
Thanks, Vlad. That would make sense if the data were consistent by period like in my first example, but in reality it's more complex. Let's add a fourth project that had activity in February, and assume Project 2 didn't have activity in March, so now our data looks like this ...
Jan, 2012 1 Midwest $100,000 $10,000 Jan, 2012 2 Midwest $200,000 $20,000 Jan, 2012 3 Southwest
$5,000 Feb, 2012 1 Midwest $100,000 $15,000 Feb, 2012 2 Midwest $200,000 $25,000 Feb, 2012 3 Southwest $50,000 $75,000 Feb, 2012 4 Southwest $25,000 $5,000 Mar, 2012 1 Midwest $100,000 $5,000 Mar, 2012 3 Southwest $50,000 $1,000
... Which means our output now should look like this ...
Midwest $300,000 $75,000 Southwest $75,000 $86,000
i think i would read the same data twice:
once with distinct, only for project id, area and budget
second will be a regular select query, something like
select region, sum(revenue) from <table name> group by region
than join between the to results by region.
i think that should answer your need.