Multiple Fact Calculation Errors

[CoginAustin]

Our data warehouse has 1 fact table for financials and 13 dimensions tied to it.

In FM we created 2 Star Scheme Groupings. Each one with 1 fact table and the 13 dimensions with a filter on TransactionType being their only difference(transtype='Budget', transtype='Actual').

We now we have 2 star scheme groupings and they work nice. I can drag the Month field from our time dimension in either grouping and get a Fact Amount from each Actual and Budget in a list report

EG Month, Account, ActualAmount, BudgetAmount

However, I want variance and % variance. In report studio I created a calculated field and did a [Budget Star].Amount-[Actual Star].Amount to get the variance.

It works perfectly. The problem comes in when I want a total by month of the variance. As soon as I create a total on [Budget Star].Amount-[Actual Star].Amount I get the error:

QE-DEF-0328 The FOR-clause in expression 'total(total([Actual star].[Actual Facts].[ActualAmount] for report) - total([Budget star].[Budget Facts].[BudgetAmount] for report) auto)' is illogical because the expression contains multiple facts.

I must be doing something wrong here since I can get a total by Account but not by overall rollup of all account by month.  Any advice is appreciated.

Thanks for any help given

[Annette]

Make sure the Fact Item / Aggregate Function property is set to "Calculated" and it should work fine.Ã,  You can set this in the properties after you set up the summarization, or you can do it by selecting "Calculated" rather than "Total" from the summarization drop-down menu when you first set up the summary.

[CoginAustin]

Thanks! I got it to work in report studio just fine by changing it to a calculated field. However, if I try to do the same in FM by adding a calculation or by adding a calculated field to a query subject I get the same error.

Is it possible to add my Variance to FM so I wont have to worry about doing it in RS?

[Annette]

You can set up the calculation in FM.  Right-click on the namespace that contains the two star schema groupings and create the calculation there.   Make sure you set the "Regular Aggregate" to "Calculated".

Your RS users might still have to set the Fact Item aggregation but at least they won't have to create the calculation every time.