Select language:

This graphic is explained in the accompanying text Example of Exception Aggregation: Counting 

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

You have loaded the following data into your InfoCube:

Region

Customer

Sales Volume

 

 

USD

NY

 

A

400,000

B

200,000

C

50,000

CA

A

800,000

C

300,000

You want to use the query to determine the number of customers for which the sales volume is less than 1,000,000 USD. To do so, you create the calculated key figure Customer sales volume <= 1.000.000 (F1) with the following properties:

  General tab page: Formula definition: Sales Volume <= 1.000.000

  Aggregation tab page: Exception Aggregation: Total, Ref. Characteristic: Customer

This query would deliver the following result:

Region

Customer

Sales Volume

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 overall result of the calculated key figure F1 is calculated as follows: Sales volume of customer A (400,000 + 800,000) -> does not fulfill condition (sales volume <= 1,000,000) -> 0: sales volume of customer B (200,000) -> fulfills condition -> 1; sales volume of customer C (50,000 + 300,000) -> fulfills condition -> 1. When totaled, this gives 2 as the overall result for F1.

A query with a drilldown by region would give the following result:

Region

Sales Volume

F1

 

USD

 

NY

650,000

3

CA

1,100,000

2

Overall result

1,750,000

2

Note

Due to the assignment of the reference characteristic Customer to the calculated key figure F1 for the exception aggregation, the query also delivers the required data without a drilldown by reference characteristic.