RSUM not producing desired result when filtering

[Silutions]

I've built a widget that uses RSUM to sum the values for multiple rows/RSUM of Fiscal Period of Year as shown in column Avg YTD Revenue shown in the two screen shots below:

Works great.  However, if I filter the pivot down to a single Fiscal Period the calculation is incorrect because the RSUM reduces to that single row.  See screen shot if filtered to period 8 below:

I have tried using the ALL() function, but of course that results in all periods for all time.  Anyone have any thoughts on how to achieve the goal.

Note that I can't use YTD() because these are custom fiscal periods on a multi-tenant system with different fiscal years & periods.  In doing some testing, it looks like it would have the same issue.

Any help greatly appreciated, Jim

 

[Angelina_QBeeQ]

Hi @Silutions ,

I'm not sure if this trick will help you on your server, but you can try. 
Write a formula with All() function and divide it by the unique count of the field you use in All() function.
Get something like this:

( RSUM( [Total DriverBasePay] ),all([Fiscal Period ID]) )/
( RSUM( [# of unique FiscalYearMonth] ),all([Fiscal Period ID]) )/
[# of unique Fiscal Period ID]

Always here to help,
Angelina from QBeeQ
[email protected]

[Silutions]

Angelina,

Unfortunately what you suggest doesn't work.  It gives incorrect results whether all periods or just one period is shown.  I'll study a little more on your approach to see if I can modify to work.  Thanks for taking a look and tell Michael I said hey!

Jim 

[Angelina_QBeeQ]

@Silutions ,

Okay, you can try another option. Check if the Analytical Engine is enabled.
If so, deactivate it (https://docs.sisense.com/main/SisenseLinux/activating-analytical-engine.htm) and check the calculation with all() function.
But it takes some time for the changes to take effect.

Kind regards,
Angelina

[Silutions]

Angelina,

Good suggestion, but we have already made the decision to leave the Analytical Engine enabled.

Regards, Jim