Forum Discussion

Silutions's avatar
Silutions
ETL
02-15-2023

RSUM not producing desired result when filtering

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

 

dashboards & reporting

4 Replies

Replies have been turned off for this discussion
  • Angelina_QBeeQ's avatar
    Angelina_QBeeQ
    ETL
    02-16-2023

    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's avatar
      Silutions
      ETL
      02-16-2023

      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 

    • Silutions's avatar
      Silutions
      ETL
      02-16-2023

      Angelina,

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

      Regards, Jim