Matrix calculation

Hi,
I have a matrix which looks as follows:
Actuals Budget
Item 1 20 50
Item 2 45 30
Item 3 20 15
I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference, because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...Right click on the cells that contain the data ("Details" as they are
called). Press on the "Add Column" button. In the new column, click
on the details part where the data goes and then put and expression in
there that will take the two data points you want and subtract them in
the way that you want.|||Thanks for the update, I have both actuals and budget in the
"matrix1_ColumnGroup4", how can I then subtract both fields from eachother?
How can I indicate to which field the database needs to look at?
Please help as this is important for me...
"SQL Guy" wrote:
> Right click on the cells that contain the data ("Details" as they are
> called). Press on the "Add Column" button. In the new column, click
> on the details part where the data goes and then put and expression in
> there that will take the two data points you want and subtract them in
> the way that you want.
>|||Ok so you have a new column, in the cell put an expression that will
subtract the two like this:
=Fields!Budget.Value-Fields!Actuals.Value
That should automatically take the budget and actual from the current
row and subtract them from each other. You would also put this in your
select statement. For example
Select actual,budget,(actual-budget) as difference
from YOUR_TABLE_NAME
where ......
Then you would have an already calculated field to use in your
matrix. Really they both go the same way. My preference is to do it
on the SQL side rather than put a formula in the matrix. It's up to
you. Hope it helps!|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||On Nov 5, 5:41 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hi,
> I have a matrix which looks as follows:
> Actuals Budget
> Item 1 20 50
> Item 2 45 30
> Item 3 20 15
> I would like to add a column with the difference between actuals and budget.
> Actuals/budget is 1 group in the matrix being the dimensionname.
> How can I calculate the difference, because if I just use the subtotal
> function it adds both totals together and I need to have the difference
> between them?
> Please help...
If this is a Matrix, this implies your data is set up like:
Items, Type, Amount
Item 1, Actuals, 20
Item 1, Budget, 50
Item 2, Actuals, 45
...
which means you can't actually do a single expression of Fields!
Actual.Value - Fields!Budget.Value.
Two ways I would do this:
1. I would add a new field to the dataset, Amount2, with an
expression of
=IIF( Fields!Type = "Budget", Fields!Amount, IIF( Fields!Type ="Actuals", -1 * Fields!Amount, Nothing ) )
This flips the sign of your budget, so that the Sum of types Actuals
and Budget gets you the difference you asked for. Then, in the
Matrix, add a subtotal on the Columns (group matrix1_Type) and use the
following expression in the Details:
=IIF( InScope("matrix1_Type"), Sum(Fields!Amount.Value), Sum(Fields!
Amount2.Value) )
What happens here is that when the Matrix is calculating the Sum() in
each column, it's doing so under the Scope of that column
("matrix1_Type"). The Total Sum at the end of the Column is done at
the Matrix Scope, so InScope() returns false, and returns the Sum of
the custom field instead.
2. Or, change the Data tab's SQL SELECT to include a union with the
calculated amount
SELECT T.ITEMS, T.TYPE, T.AMOUNT FROM TABLE_NAME T
UNION
SELECT T.ITEMS, 'Total' AS TYPE,
SUM( CASE T.TYPE
WHEN 'Actuals' THEN T.AMOUNT
WHEN 'Budget' THEN -T.AMOUNT ELSE NULL END ) AS AMOUNT
FROM TABLE_NAME T
WHERE T.TYPE IN ( 'Actuals', 'Budget' )
GROUP BY T.ITEMS
then just refresh your existing Matrix and a Totals column will appear
with the total you requested.
-- Scott