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’
THEN
[Measures].[Profit by Expected Close date] *.33
ELSE
[Measures].[Profit by Expected Close date]
END
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:
CASE WHEN
[Vendor].CurrentMember.Name<>"$total_aggregate"
THEN
--your current formula comes here
END
