Accumulation percentage based on 12 month totals!

[marcus_steggall]

Afternoon, hope all are well

I am trying to do a cumulative bar graph, across a rolling 12 months!

On the Expressions tab, it's easy to change each expression to "Accumulate" 12 steps (months) back.

However for the final Profit %, this won't work as is; percentages just become greater.

Even if you did no accumulation on the expressions tab, this would only do the calculation for the month only.

As you can see on the two straight tables, the 2018 Apr percentage is 53% which is correct for the month.

However a cumulative percentage for last 12 months to 2018 Apr, should be 34%.

How can I get that percentage working correctly, based on 12 month totals, at the month selected?

Any help or advice, appreciated

[sunny_talwar · Accepted solution]

Try this with no accumulation

=RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,

Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

or

round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Profit), 0, 12))

/

RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,

Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

or

round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Revenue), 0, 12))

[marcus_steggall]

ahhh the range above function, excellent am pleased to report this also works.

Nice one pal, appreciated as always for quick response ands solution