Totals wrong when using a total row

Hi Folks,

I have a calculated member that works great, but the total (when using the total button) is wrong from the amounts. It is taking the total of all before the *.33 is run. So my account managers are very happy as they see a larger number than they should.

My member is this:

CASE WHEN [Vendor].CurrentMember.Name = ‘Obsidian Code’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Data’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Teams’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Managed Services’ OR
[Vendor].CurrentMember.Name = ‘Obsidian Compute’ OR
[Vendor].CurrentMember.Name = ‘Autumn Leaf Code’ OR
[Vendor].CurrentMember.Name = ‘Autumn Leaf Compute’ OR
[Vendor].CurrentMember.Name = ‘Autumn Leaf Data’
[Measures].[Profit by Expected Close date] *.33
[Measures].[Profit by Expected Close date]

Pic of column and inaccurate total:

Any suggestions?

Figured it out! It was my ELSE line that was messing things up. Now to spend some time understanding why…


Sorry for the late update; you are right that the ELSE branch is the reason for not giving the proper total. The calculation evaluates the condition of the “CASE” and at total level it always enters the “ELSE” branch since the Vendor name does not match any of those mentioned in the condition (there is no specific Vendor at the Total). The result is the value of the measure for the “All Vendors” (which should ignore the *.33 case).

A workaround is to use a hidden option to tell the measure how to behave in the case of the total. There is also a small hack in the solution, that once you tell in the formula that there should be “no value” for the total, the total line will output the sum of cells.

Please, try to wrap your current formula in another CASE construction:

 --your current formula comes here

Janis, eazyBI support