Totals wrong when using a total row

Questions & Answers
1

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’
THEN
[Measures].[Profit by Expected Close date] *.33
ELSE
[Measures].[Profit by Expected Close date]
END

Pic of column and inaccurate total:

image
image.png132×681 5.18 KB

Any suggestions?

2

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

3

Hi,

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

Kindly,
Janis, eazyBI support

4

Hi @janis.plume

I have a similar problem. I need to have the total sum of the rows, but it’s showing something else.
image

I even tried with the Cumulative, but it shows the same.

Formula is below

  CASE WHEN [Measures].[Issues resolved] > 0 THEN
    [Measures].[Total resolution workdays] 
    /  [Measures].[Issues resolved]
      *24
      *60
  END
-- Formatting is in days, hours, mins (that's why the multiplications)

Any ideas?

Thanks

5

Hi,

This formula should give the total once you use the annotation:

-- annotations.total=sum 
CASE WHEN [Measures].[Issues resolved] > 0 THEN
    [Measures].[Total resolution workdays] 
    /  [Measures].[Issues resolved]
      *24
      *60
  END
-- Formatting is in days, hours, mins (that's why the multiplications)

Kindly,
Janis, eazyBI support

1 Like