cancel
Showing results for 
Search instead for 
Did you mean: 

When a Column Calc and a Row Calc Intersect, which one does Analysis choose?

Former Member
0 Kudos

Hi,

Product: BI 4.0 SP8.3

Database: MS SQL Server 2012

I have created a Total column (last column on the right) that contains a calculation that Adds the values of all the columns before it.

I have created a Gross Margin % row that contains a calculation that does a Percentage Share between two specific columns above it.

As can be shown in the screenshot below, Analysis looks to be confused when it comes to choosing which calculation to perform when the Total column and the Gross Margin % row intersect. I want it to perform the Gross Margin % calculation, which would have resulted in "66%". Unfortunately, Analysis displayed the number "10", which doesn't make sense:

How can I tell Analysis to perform the Percentage Share calculation and not the Addition calculation?

Cheers,

Michael

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Michael,

I don't believe A-OLAP explicitly assigns a solve order and instead relies on SSAS to make the decision. See below.

In the case of multiple calculations having the same solve order, the following formula precedence is used:

  1. Calculated cells
  2. Custom rollup formulas
  3. Custom and calculated members
  4. All other calculations

Calculated cells take precedence over all other calculations in the case of solve order conflict. If multiple calculations occur within the same category, the declaration order of the calculation is used. For example, if two calculated cells definitions have the same solve order for the same calculation pass, the declaration order determines which is evaluated first.


http://technet.microsoft.com/en-us/library/aa216755%28v=sql.80%29.aspx


Hopefully, this information can be used to achieve the order you are looking for.


Regards,

Philip

Former Member
0 Kudos

Thanks for the reply, Philip.

Since Analysis can't handle intersecting calculations properly, I removed the Total calculation and replaced it with the All level. This allowed the Gross Margin % calculation to display correctly.

Question: Is there anyway I can rename the All level column header name to "Total"?:

Thanks,

Michael

Answers (1)

Answers (1)

Former Member
0 Kudos

I believe Analysis is performing the Add calculation instead of the desired Percentage Share calculation. Anyone know how I can tell Analysis to perform the row's Percentage Share calculation?