Robert Hood Posted September 5 Share Posted September 5 Hi all, I have a cross table that displays different product columns with a running total by month for budget dollars and actual spend dollars. I can't seem to get the table to subtract the budget from actuals with a running total by month. I'd like to end up with a differnce line for each month and product. Sum([Budget]) THEN Sum([Value]) OVER (AllPrevious([Axis.Columns])) - Sum([Actuals]) THEN Sum([Value]) OVER (AllPrevious([Axis.Columns])) AS Difference Link to comment Share on other sites More sharing options...
Solution Robert Hood Posted September 5 Author Solution Share Posted September 5 Figured out! This works all day long. I assumed I needed to accumulate both sides of the equation. Sum([Budget]) - Sum([Actuals]) THEN Sum([Value]) OVER (AllPrevious([Axis.Columns])) AS Difference 2 Link to comment Share on other sites More sharing options...
Olivier Keugue Tadaa Posted September 6 Share Posted September 6 Hi Robert, Yes indeed, when using the THEN you should first perform the aggregation and any other calculations and use the [value] after the THEN statement. This is very useful when working with in-data sources since it will optimize the query sent to underlying databases. Since the [budget] and [actuals] are calculated on the same axes AllPrevious([Axis.Columns]), using the second expression is easier and more effective. In some other situations, this can require more complex expressions Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now