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 |
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 |
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.