Counting Exception Aggregations

The following scenario shows how you can count the results of a calculated key figure in the BEx Query Designer.

You have loaded the following data into your InfoCube:

In the query you want to determine the number of customers where the revenue is under 1,000,000 USD. For this you need to create the calculated key figure Revenue Customer <= 1,000,000 (F1) with the following properties:

• Tab page General: Formula Definition: Revenue <= 1,000,000

• Tab page Aggregation: Exception Aggregation: Total; Reference Feature: Customer

  This query would deliver the following result:

  Region	Customer	Revenue	F1
  		USD
  NY	A	400.000	1
  B	200.000	1
  C	50.000	1
  Result	650.000	3
  CA	A	800.000	1
  C	300.000	1
  Result	1.100.000	2
  Overall Result	1.750.000	2

  Note

  The grand total of the calculated key figure F1 is calculated in the following way:

  Revenue Customer A (400,000 + 800,000) does not fulfill the condition (Revenue <= 1.000.000), the result is 0,

  Revenue Customer B (200,000) does fulfill the condition, the result is 1,

  Revenue Customer C (50,000 + 300,000) does fulfill the condition, the result is 1,

  when totalled together the grand total for F1 is 2.

  A query with the breakdown according to region would give the following result:

  Region	Revenue	F1
  	USD
  NY	650.000	3
  CA	1.100.000	2
  Overall Result	1.750.000	2

  Note

  By assigning the reference feature Customer during the exception aggregation of the calculated key figure F1, the query also returns the required data without a breakdown according to reference features.