How to summarize sales for a product from transactions that contain another product?

ArunSanthosh · March 17, 2022, 1:45pm

Customer question:
I think what I want is currently not possible in QuickSight but would like to check if I’m correct.

We have data like (I’m only listing the interesting fields):
transactionID | Productgroup | Price |
1 | 10 | 1.00 |
1 | 15 | 2.00 |
2 | 10 | 3.00 |
2 | 15 | 4.00 |
3 | 10 | 5.00 |
3 | 25 | 6.00 |
3 | 30 | 7.00 |
etc.

What I would like to do is ‘select’ all transactions which contain a ProductGroup of X and be able to sum() all the prices of those transactions where ProductGroup is Y.

So as example based on the example data:

I want to know the sum of the prizes for ProductGroup 15 in transactions which contain a ProductGroup of 10. Which would mean I select transactionID 1 and 2 (because they contain ProductGroup 15) and sum 1.00 and 3.00 (because those contain ProductGroup 10)
transactionID 3 is ignored in this sum() because it doesn’t contain ProductGroup 15 in any of the rows.

ArunSanthosh · March 17, 2022, 1:55pm

Here’s the solution.

You can check for presence of a product group within a transaction by utilizing countOver calculation in PRE_FILTER mode. This will stamp the count of the product group being searched for against all the records for each transaction. Thereafter, you can sum the price for the product you want to summarize by using a compound condition in sumIf. Sample calcs and screen shot given below.

Core product count per transaction

countOver(
        ifelse({Product Group}=${pSearchProductGroup},
                {Product Group},
                NULL
        ),
        [{Transaction ID}],
        PRE_FILTER
)

Price for product to summarize

sumIf(Price, 
    {Core product count per transaction} > 0 
    and {Product Group} = ${pSummarizeProductGroup}
)

Regards,
Arun Santhosh