Computing percentages in a stacked bar chart

[#1]

Hi,

I am working on a stacked bar chart.  I have two dimensions.

Dimension 1 is a group named Year.  It has 3 drill down levels: Fiscal Year, Quarter, and Month

Dimension 2 is a calculated dimension based on the values in a variable:

=aggr(if($(ComputedBench)='25th', $(ComputedBench),

if($(ComputedBench) = '50th', $(ComputedBench),

if($(ComputedBench) = '75th', $(ComputedBench),

if($(ComputedBench) = 'Over 75th', $(ComputedBench))))),

FACNUM, Year)

ComputedBench is a variable that contains 25th, 50th, 75th, Over 75th or Null.

I tried this expression to compute the percentages, but no data was displayed in my chart:

=count($(ComputedBench))/aggr(NODISTINCT Count($(ComputedBench)),Year)

I  tried changing it to this expression thinking that counting a value from a variable may be the issue, but the calculation seems to be off since the 4 stacks (based on dimension 2) on my bar chart do not add up to 100%. 

=Count(NormTotalExpenseEVS)/aggr(NODISTINCT Count(NormTotalExpenseEVS),Year)

NormTotalExpenseEVS is a field in a table.  If a center has NormTotalExpenseEVS filled in, then ComputedBench should definitely have a value other than NULL.

Does anyone have any suggestions about how to make this work?

Thank You!

[Oleg_Troyansky]

Try something like this:

=Count(NormTotalExpenseEVS)/Count( TOTAL <Year> NormTotalExpenseEVS)

or, for a dynamic group, try this:

=Count(NormTotalExpenseEVS)/Count( TOTAL <$(=GetCurrentField(MyGroup))> NormTotalExpenseEVS)

when calculating subtotals by chart dimensions, using TOTAL works much better than using AGGR.

cheers,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

[#3]

Thanks for the response. I tried the =Count(NormTotalExpenseEVS)/Count( TOTAL <Year> NormTotalExpenseEVS).  My chart showed 100% for all of the dimensions, so I dumped the expressions into a pivot table to look at the pieces of the expression (results are below). 

Month	Quartile	TotalExpenseperAPD	Numerator	Denominator
October	Over 75th	100%	31	31
October	75th	100%	46	46
October	50th	100%	54	54
October	25th	100%	65	65

The numerators are correct, but the denominator should be equal to the sum of the numerators for all quartiles combined, which in this case should be 196 for each of these.  It looks like it is only considering the quartile in the current dimension when computing the denominators. Any ideas how to get around this?

[Oleg_Troyansky]

Shannon,

if you first dimension is Month, then you should use Month as a qualifier for the TOTAL:

Count(NormTotalExpenseEVS)/Count( TOTAL <Month> NormTotalExpenseEVS)

Or, use GetCurrentField() to make the expression work with a dynamic group