I will try to make this clear with below example.
We would like to know the average amount of Sub-Tickets per Ticket. For this we have a Data Source which contains Tickets and related Sub-Tickets, with the default Key-Figure: Counter.
Counting the amount of Sub-Tickets is not a problem. You can use the standard Counter for that. However, we cannot use the same Counter to count the amount of Tickets. As you can see in above example there are 5 TicketID’s displayed but only 2 are unique. The standard Counter will return the number 5 for the amount of TicketID’s. in current selection.
What we would like to see is that the TicketID is only counted twice. Normally you would need to change your selection and remove the Sub-TicketID so only the in 2 TicketID’s are counted. This way you lose the amount of Sub-TicketID’s in you overview.
To achieve that both ID’s are counted we need to have a second Key-Figure that can count the unique TicketID’s in our Data Source. This is where the use of Exception Aggregation comes in.
Setting up the Exception Aggregation Key-Figure
To create the new Key-Figure that uses Exception Aggregation you need to:
- Go to: Business Analytics - > Design Key Figures.
- Press the New Button and below screen appears.
- Define a new name for the Key-Figure, for instance “Unique TicketID”
- In the Data Source you select the Data Source where you want to use the new Key-Figure
- Keep the type as Calculated Key-Figure. This is important because Exception Aggregation only works for this type of Key-Figure.
- Now press the Next button.
In below screen you can see you Counter Key-Figure on the left.
- Drag it to the formula entry field as shown below.
- After you have done this press Next again.
You will now see the Properties screen for the new Key-Figure. Here the Exception Aggregation is done.
- In the drop down select value: “Counter for All Detailed values that Are Not Zero, Null or Error”
- In the characteristic you select the field for which you want to count the unique values only, in our case TicketID.
After selecting the characteristic you are almost ready.
- Press the Finish button to create the new Key-Figure.
As you can see the Unique TicketID now only counts 2 TicketID’s. The Counter can still be used to calculate the amount of Sub-TicketID’s. These 2 Counters can now be used to calculate the average amount of Sub-Tickets per Ticket. You no longer need to remove values from your selections to get the correct totals you want to see.