Column dependency will guard the summary from double counting but expressions which use facts from different grains will produce what seems like incorrect results. although logically correct given what is being asked of it. After all, the sum of 914,965 less 1,224,340 is -309,375.

You would need to summarize the values up to the grain of the other fact table, with the most likely approach, because it will create an object which is usable in many places, to be to create a summary table (either in your data base or in the module).

Here is a report where I have facts from my sales fact table and my product forecast fact table.  The former has a grain of day.  The latter has a grain of month.  Because of that, a calculation to determine the variance between the two facts will be using day level sales quantity values and subtracting from that the month level expected volume values.  I also have a summary table which has, through my YearMonthKey, which is at the month level, summarized the values of quantity to the month level.  As you can see, the calculation which determines the variance between that quantity and expected volume produces a month level value

------------------------------
IAN HENDERSON

Original Message:
Sent: Thu March 24, 2022 02:06 AM
From: Robert Dostal
Subject: Calculation related question

Hello,

if your data source is a data module, you need to apply column dependencies as both tables are at different grain levels.
https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=metadata-column-dependencies

If it's a package from Framework Manager, then the author of this package needs to apply determinants.
https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=subjects-determinants

Both techniques avoid double counting on data.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Wed March 23, 2022 05:17 PM
From: Hiren Vashi
Subject: Calculation related question

Hello,

I am new to this group and new to Cognos, so if this question is not asked to the right group then please pardon my ignorance.

I have two tables as explained below.

Hi Hiren, 

Took you data and put it into an Excel spreadsheet with budget and expense tabs and I created an Accounting year tab. A relationship between Accounting Year to Budget and a relationship to expense was created. Also a relationship from budget to expense was created which I deleted.

I suspect in your data module you have a 1 to many relationship between budget and expense. This is causing the double counting budget amount.

By using accounting year from the accounting year table I created aggregation occurs correctly and calculation 

Budget.Budget_Amount -  Expenses.Invoice_Amount  

returns the correct remaining budget amount. 

hope that helps