Calculated field based off two totals

[mdb_talon]

Hello everyone,  I am new to Cognos and new to the forums.  Hoping I can get some help.  I did a lot of googling and searching forumns, but so far have not been able to resolve this.  Sorry about all the information I am not sure how to best ask the question so giving lots of background.  Question is highlighted below.

I am trying to create a report as shown below.  This is a crosstab against a DMR model with drilldown.  The "F A" and "F B" columns below are different financial forecasts that we prompt for.  So the user can select to show both forecast A and B on the report.  Below that we have "Brand Elist" which is basically our organizational structure that they can drill down into.  The two total columns next to the Brand Elist total up the org structure for that specific forecast.  So we may have 10 departments that show up under Brand Elist and the Total column would obviously show us the total (basically it shows us the previous level in the hierarchy as they drill down). 

The Variance column is just "F A" - "F B".  So if we had drilled down to the department level we may have 10 departments for "F A" and 10 departments for "F B".  The variance will show the difference between the A and B forecast for each department.  The Total column next to that gives the complete variance.  So if each of 10 departments varied by $5 then the Total column there would show $50.

All of the above I have working other than for the last 4 rows of the report.  Those are all calculated amounts that are expressed as a percentage.  So "NR%" for example is [TOTAL NET REVENUE] / [TOTAL GROSS REVENUE].  If I am at a node in the Brand Elist that only has one entity this works.  For example if we are only looking at the NY office comparing forecast A to forecast B and the net revenue changed from 24% to 23% the variance calculation would accurately show 1%.  However if the hierarchy is showing 2 items in the Brand Elist (say NY and LA office) then it will not work the way I need.  It will accurately do the variance for each office, but then the total will add the two variances.  So if I assume each office both had the exact same financial numbers and their individual variance was 1% then the total will show 2%.  The real variance is only 1%.  A bigger issue than that though is if one office only has 500k revenue and the other office has 10,000k revenue then the percentages should not be weighted the same.

QUESTION -- finally got to the question........

So what I have been trying to do is make another variance column that shows the difference between the Total columns "F A" and "F B" rather than a sum of the differences under the "Variance" column.  I have tried many ways, but just cannot get it to work.  Any help would be greatly appreciated.

[MFGF]

Hello everyone,  I am new to Cognos and new to the forums.  Hoping I can get some help.  I did a lot of googling and searching forumns, but so far have not been able to resolve this.  Sorry about all the information I am not sure how to best ask the question so giving lots of background.  Question is highlighted below.

I am trying to create a report as shown below.  This is a crosstab against a DMR model with drilldown.  The "F A" and "F B" columns below are different financial forecasts that we prompt for.  So the user can select to show both forecast A and B on the report.  Below that we have "Brand Elist" which is basically our organizational structure that they can drill down into.  The two total columns next to the Brand Elist total up the org structure for that specific forecast.  So we may have 10 departments that show up under Brand Elist and the Total column would obviously show us the total (basically it shows us the previous level in the hierarchy as they drill down). 

The Variance column is just "F A" - "F B".  So if we had drilled down to the department level we may have 10 departments for "F A" and 10 departments for "F B".  The variance will show the difference between the A and B forecast for each department.  The Total column next to that gives the complete variance.  So if each of 10 departments varied by $5 then the Total column there would show $50.

All of the above I have working other than for the last 4 rows of the report.  Those are all calculated amounts that are expressed as a percentage.  So "NR%" for example is [TOTAL NET REVENUE] / [TOTAL GROSS REVENUE].  If I am at a node in the Brand Elist that only has one entity this works.  For example if we are only looking at the NY office comparing forecast A to forecast B and the net revenue changed from 24% to 23% the variance calculation would accurately show 1%.  However if the hierarchy is showing 2 items in the Brand Elist (say NY and LA office) then it will not work the way I need.  It will accurately do the variance for each office, but then the total will add the two variances.  So if I assume each office both had the exact same financial numbers and their individual variance was 1% then the total will show 2%.  The real variance is only 1%.  A bigger issue than that though is if one office only has 500k revenue and the other office has 10,000k revenue then the percentages should not be weighted the same.

QUESTION -- finally got to the question........

So what I have been trying to do is make another variance column that shows the difference between the Total columns "F A" and "F B" rather than a sum of the differences under the "Variance" column.  I have tried many ways, but just cannot get it to work.  Any help would be greatly appreciated.

Hi! Welcome to Cognoise!! It's really nice to see a detailed explanation like this - it (hopefully) makes answering much easier.

I'm hoping this is something as simple as the solve order. Column calculations are done prior to row calculations by default, so your variance is being calculated, then the sum in the rows is totalling the variances. You can change the solve order by selecting the Variance calculation and finding its Solve Order property. Set it to 2 and see if this fixes the issue.

Cheers!

MF.