We have a corporate - region - branch dimension. Measures are loaded at the branch level.

We have a measure for taxes. It is a defined field = if revenue gt 0 then (revenue * 40%) else 0.This works fine at the branch level.

However - when calculating taxes for the region, I can't figure out what aggregation method to use, because the same formula applies to the region level.

If there are 2 branches in a region:
Branch A has revenue of $100 and therefore has taxes of $40.
Branch B has revenue of ($100) and therefore has taxes of $0.

The region will show $40 in taxes - the rollup of the two branches. However - the region tax should be $0. The rolled up revenue for the region is $0 - and therefore the tax calculation should be $0 for the region.

Is there a way to do this without having to use 3 seperate measures (as region rolling up to corporate will also be wrong)?

Thanks,
Chris

Hey Chris,

Seeing as this formula is consistent at all levels, I beleieve you can handle this with a simple modification to the core a_compute_rpt_vars.fex program which is in the mainstreet folder. You didn't say what PMF release you were on, but this code has been fairly stable for the last few releases, so you are likely to see something like this:

      COMPUTE VAL_ACTUAL&QUAL/D20.2      = IF (MEASURE_AGGREGATE_TYPE EQ 'A') THEN ACTUAL
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'P') THEN (ACTUAL_NUMERATOR /     ACTUAL_DENOMINATOR)  * 100.0
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'C') THEN (ACTUAL_NUMERATOR / ABS(ACTUAL_DENOMINATOR)) * 100.0
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'R') THEN (ACTUAL_NUMERATOR /     ACTUAL_DENOMINATOR)
                                                                         ELSE 0.0 ;

What you can do is to add some code for the taxes Measure:

      COMPUTE VAL_ACTUAL&QUAL/D20.2      = IF  (MEASURE_SERIES EQ nnn) AND (ACTUAL GT 0) THEN ACTUAL * 0.40
                                 ELSE IF (MEASURE_SERIES EQ nnn)                         THEN 0.0
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'A') THEN ACTUAL
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'P') THEN (ACTUAL_NUMERATOR /     ACTUAL_DENOMINATOR)  * 100.0
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'C') THEN (ACTUAL_NUMERATOR / ABS(ACTUAL_DENOMINATOR)) * 100.0
                                 ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'R') THEN (ACTUAL_NUMERATOR /     ACTUAL_DENOMINATOR)
                                                                         ELSE 0.0 ;
 

where nnn is the MEASURE_SERIES for your Taxes Measure. Note that when you load up this measure, you would load the Revenue figures. As with all other PMF customizations, put any modified code in a pmfcustom app folder - and you will have to pay attention during application upgrades to pick up any other changes that might have been made to the base program

EricH

Hi, Eric-

I modified our a_compute_rpt_vars.fex file to have this:

COMPUTE VAL_ACTUAL&QUAL/D20.2 = IF (MEASURE_SERIES EQ 'Post Tax Earnings') AND (ACTUAL GT 0) THEN ACTUAL * 0.60
ELSE IF (MEASURE_SERIES EQ 'Post Tax Earnings') THEN ACTUAL
ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'A') THEN ACTUAL
ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'P') THEN (ACTUAL_NUMERATOR / ACTUAL_DENOMINATOR) * 100.0
ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'C') THEN (ACTUAL_NUMERATOR / ABS(ACTUAL_DENOMINATOR)) * 100.0
ELSE IF (MEASURE_AGGREGATE_TYPE EQ 'R') THEN (ACTUAL_NUMERATOR / ACTUAL_DENOMINATOR)
ELSE 0.0 ;

However, I'm receiving the following error on the page that displays this measure:

0 ERROR AT OR NEAR LINE 50 IN PROCEDURE A_COMPUTE_RPT_VARS
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Can you shed some light on this?

Thanks
Tyson

The MEASURE_SERIES field you're referencing contains a reference number for the measure, not the name, hence you're getting a type mismatch. That's what the error is telling you.

You want MEASURE_SERIES_NAME, not MEASURE_SERIES. Your code will then work.

Where would I find this reference number? Thanks for the quick response, Bob

Tyson

Got it working, with both Measure_Series_Name and the Measure_Series. I always wondered what that number next to the measure name was.

FYI, for all those reading this, the number shows up on the Measure Loader or the Measure Entry Setup panels, immediately to the left of the Measure Name (top left of the panel, under the tabs and buttons). Or you can get this information off the PMF Administration reports.

Hi, Bob & Eric-

I've got another twist for you. We want the same behavior as the above, but in the a_compute_rpt_vars.fex conditional we need to check the ACTUAL of another measure. The problem is that in my conditional, I can't use the ACTUAL of the measure because the value I'm analyzing isn't part of the calculation of the measure itself. I tried putting the non-current-measure calculation/value in the BENCHMARK field, and referencing THAT in the fex, but it didn't work.

Any ideas?

Thanks, Gentlemen
Tyson

Hey can I use EricH's example to also affect the targets for this measure?

Also does anyone have any thoughts on the above question about evaluating the actual of a different measure series to affect the display of the current measure series?

Hey Tyson,

Unfortunately there is no simple way to do "inter-Measure Series" calcuations - at least not that I can think of. You can do two passes of the data, then use FOCUS JOIN or MATCH logic.

However, you may be able to apply formulas to any of the Target Fields (BENCHMARK, FORECAST, STRETCH, or TARGET). The equivalent code for the Target Fields is in a_compute_rpt_vars_i1.fex. You will have to use your FOCUS skills here since the code is more complex. Without going into details, we only do the calculations for those Target Fields that are selected for a particular report, so there is a fair amount of Dialogue Manager logic built into this routine.

EricH