Showing Top 5 with Others as percentage for a series of months

[kdmarkee221]

I need to get a Top 5 (and Others) working with percentages of totals as a straight table (pivot would be ideal if it is even possible) and a stacked bar chart.

I can accomplish the top 5 (and Others) via the Dimension Limits feature for the dollar amounts as shown below.

But I really want to report on the percentages (per monthly totals) as shown below but cannot get it working.

My expression for the dollars is: 

sum({$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)

My expression for the percentage, which does not work, is:

sum({$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)
/
sum(TOTAL{$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)

I appreciate any help I can get.  Thanks.

[marcus_sommer]

You may need to play a bit more with the TOTAL statement maybe by adding dimensions to it like:

TOTAL <Year, Month>

to ignore not the entire dimensions else considering further the listed ones. Helpful may also be not check this in the rate calculation else to use the total ones as a second expression which makes it much easier to comprehend the results - if it worked on the partial level you could finally adjust the final view.

[ali_hijazi]

I assume you need to amend the expression of percentage as follows:
sum({$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)
/
sum(TOTAL<[Service Month],[Service Year]>{$<TableKey = {'ABC'}, TYPE={'Charges'}, ROLLING12_TIMEPERIOD_SD={'Current'}>}AMOUNT)
Assuming that Service Month and Service Year are the names of the dimension and not labels in the chart (unless they are the same)

[kdmarkee221]

I have been experimenting with TOTAL<xxx> and was not getting anywhere.  I can report however that I did get this working but I'm not sure why this mattered.  Instead of using 2 separate fields in my objects and expressions etc ([Service Year] and [Service Month]), I used a single field called [Month & Year], then used TOTAL<[Month & Year]> and it worked.

[marcus_sommer]

In general worked TOTAL with n listed dimensions and also the order of the listing has no impact. Therefore the cause must be an applying of a wrong dimensions maybe by calling the label and not the field-name or a similar field which is not the used as object-dimension.

Further important to TOTAL statements and also aggr-dimensions or set analysis-conditions - it must be native fields from the data-model and not calculated dimensions, like: month(MyDate).  

[ali_hijazi]

In the total<x,y,z> the x,y, and z should be available in the dimensions of the chart 

You cannot use a dimension outside the chart