
1. Re: How to Sum Column for Values Computed by Formula
Joshua Milligan Nov 23, 2016 8:42 AM (in response to Wayne Ooi)2 of 2 people found this helpfulWayne,
The difficulty here is using table calculations which work great, except in the Grand Total line where the level of detail is different than the rest of the view (which is at a Segment level of detail, but the Grand Total doesn't have it).
Sometimes, you can create a copy of the dimension that defines the level of detail, place that on Detail and force the Grand Total to that level (then roll things up using a Window_Sum() function). But that's going to get quite complex given all your grand totals.
So, in this case, you might want to consider getting rid of all table calculations (e.g. TOTAL) and instead rewrite a few calculations as LoD calcs (there are only 3!).
[Mix Vol  FC] changes from:
SUM([VolFC]) / TOTAL(SUM([VolFC])
to
SUM([VolFC]) / SUM({EXCLUDE [Segment] :(SUM([VolFC]))})
Notice that I changed the TOTAL to an EXCLUDE LoD. Given that Segment is the only dimension in the view, excluding it gives the same result as a TOTAL. This becomes important in a bit, where we'll fix the Grand Total and won't be able to use table calcs...
(BTW, if your final view is more complex, you'll have to determine if there are other dimensions that need to be excluded)
[Mix Vol  TY] changes from:
SUM([VolTY]) / TOTAL(SUM([VolTY])
to
SUM([VolTY]) / SUM({EXCLUDE [Segment] : (SUM([VolTY]))})
And finally...
[Mix Effect] changes from:
([RateTY]  TOTAL([RateTY])) * [Mix Var]
to
{INCLUDE [Segment] : ([RateTY]  SUM({EXCLUDE [Segment] : ([RateTY])})) * [Mix Var]}
Here's what it's doing:
 The basic formula is still there ([Rate TY]  Total Rate TY) * Mix Var
{INCLUDE [Segment] : ([RateTY]  SUM({EXCLUDE [Segment] : ([RateTY])})) * [Mix Var]}
 What changed is that the total is calculated by using an Exclude (as explained above) but the entire calculation is done as an INCLUDE, bringing Segment back into the level of detail. Everything is already at a Segment level (so it works as it did before), but the Grand Total which was not previously at the Segment level (and thus was wrong) is now calculated at a Segment level and is right!
Hope that helps!
Joshua

2. Re: How to Sum Column for Values Computed by Formula
Wayne Ooi Nov 24, 2016 4:19 AM (in response to Joshua Milligan)Hi Joshua,
Thank you very much for your help. I have updated my report accordingly to your suggestion and the report works fine now.
Thank you also for explaining why using Include and Exclude of LoD function in the calculation. It's good to know the differences.
Thanks,
Wayne.

3. Re: How to Sum Column for Values Computed by Formula
Priscila Marin Jan 30, 2017 4:32 AM (in response to Joshua Milligan)Hi Joshua
Very usefull your explanation. But I have a doubt, in my case I have this formula:
%Dif = (ZN([*teste_formula])  LOOKUP(ZN([AVGITEM]), 1)) / ABS(LOOKUP(ZN([AVG_ITEM]), 1))
AVG_ITEM = sum({ INCLUDE [ITEM] : sum([VLR TOTAL LIQ])} ) / sum({ INCLUDE [ITEM] : sum([QTDE])} )
Var %Dif * Fat2016 = [*% Dif] * sum({ EXCLUDE :sum([Fat_2016])})
I need that column "Var %Dif * Fat 2016" results 214
Can you help me?
Thank you
Priscila Marin