2 Replies Latest reply on Aug 26, 2012 3:02 PM by Joe Mako

# Filters and Custom values

A particular variable A is calculated as a weighted average using variables B and C (weight) . Now, the data is at two levels Region (North America, Latin America etc.) and Country (Australia, US, India etc.). I have created this field A as a calculated field using the following formula:

Sum(B*C)/ Sum(C)

If I create a quick filter for just region and select a region say region 1, the value of A will be aggregated based on the B and C values for all the countries in the selected region (region 1).

The problem is, when an user selects the region filter as (All), which is nothing but all the regions or the worldwide level, the calculation of variable A should be designed differently. Now that I have A values for individual regions which are calculated using variables B and C, I want to create the value of A at worldwide level using the following formula

A(region 1)*0.2 + A(region 2) *0.25 + A(region 3) *0.4+ ...

where,

A(region i) is value of A calculated for region i

0.2, 0.25, 0.4 can be giveN as user inputs or manually into a formula

Thanks,

ManiDeepak

• ###### 1. Re: Filters and Custom values

Hi Manideepak,

One suggestion might be to define the calculations as desired, then create a parameter that changes which calculation is referenced.

Or to use a parameter as the filter to determine which region is present and which is not.

Hope this helps a bit!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Filters and Custom values

How about a calc field like:

IF MAX([Region])=MIN([Region]) THEN

SUM([Value]*[Weight])/SUM([Weight])

ELSE

SUM(IIF([Region]="A",[Value]*[Weight],0))/SUM(IIF([Region]="A",[Weight],0))*.10+

SUM(IIF([Region]="B",[Value]*[Weight],0))/SUM(IIF([Region]="B",[Weight],0))*.15+

SUM(IIF([Region]="C",[Value]*[Weight],0))/SUM(IIF([Region]="C",[Weight],0))*.25+

SUM(IIF([Region]="D",[Value]*[Weight],0))/SUM(IIF([Region]="D",[Weight],0))*.5

END