Calculating the number of shopping baskets that include 2 specific product using SAP Analytics Cloud

I was recently working on a reporting challenge where I wanted to know the number of shopping baskets that include 2 specific products.  The solution is beautifully simple when you know how, so here’s how using Conditional Aggregations.

The dataset is for 30,981 individual orders with 36,040 different products in those orders.  For this example, I am looking to ask the question how many orders included BOTH Apple Juice & Orange Juice.

The first step is to create a measure that counts the number of orders that have Apple Juice in them using an Aggregation.  Then you use a conditional aggregation where you select just for the product Apple Juice.

A Conditional Aggregation allows you to specify when the aggregation is applied and what conditions are required for the aggregation to be applied.

Repeat the same process for identifying the number or orders with Orange Juice in them

Now to bring the two new measures together into one “Flag” that we sum up in the final step.  Create a calculated measure that checks for where Apple Juice has a value and where Apple Juice has a value and returns the value 1.

If ( [#Apple Juice] >0 and [#Orange Juice]>0,1,0)

Then the final step is to Sum the measure in the previous step as it would simply show a list of the value 1 when used in a chart with the Order Id

So, in this example we can see that 733 orders have both Apple Juice and Orange Juice in their shopping basket.