Sum of Rows Equivalent in a single value

[Anonymous]

Hi,

I have a situation where I calculate departed over Head Count on each month. Simple Formula with Sum(DepartedCount)/Sum(HeadCount) will provide the required calculation. I need to accumulate these values for each month over a fiscal year to date. ie. If the user select Jan as the month, values from July to Jan should be accumulated similar to sum of rows in an expression in a straight table. The situation is complicated due to calculating months upto fiscal year to date using set analysis by ignoring monthly and yearly selections.

See sample attached.

The expected outcome is 13.18% in a text box, for the selections:

CalendarYearDisplay = 2016

Month = Jan

Appreciate any assistance.

[sunny_talwar · Accepted solution]

Try this:

=NUM(

Sum({$<Month=,CalendarYearDisplay=, FiscalYear={"$(=max(FiscalYear))"},FiscalMonthNumber={"<=$(=max(FiscalMonthNumber))"}>}Aggr(

Sum({$<Month=,CalendarYearDisplay=, FiscalYear={"$(=max(FiscalYear))"},FiscalMonthNumber={"<=$(=max(FiscalMonthNumber))"}>}DepartedCount)/

Sum({$<Month=,CalendarYearDisplay=, FiscalYear={"$(=max(FiscalYear))"},FiscalMonthNumber={"<=$(=max(FiscalMonthNumber))"}>}HeadCount)

,FiscalMonthYear))

,'#,##0.##%')

[Anonymous]

Many thanks for prompt response.