Calculating sales of add-on products

[petejigsaw]

I'm working on measuring penetration of sales of add-on products by order so that I can prepare a dashboard for each salesperson showing penetration of 'add-ons' in various product groups.

The below table shows my raw data.  It lists the SQL database lines by order (multiple entries per order item - that's just how our system records transactions), including a column showing if the item is a 'qualifying product' for the 'add-on' type. It then has a column showing whether the 'add-on' was sold.

My desired output is at the bottom - essentially I need to be able to show:

• Total orders
• Total orders that have a qualifying product on them
• Total orders which include the 'add-on'

Order No.	Stock_ID	Item	Qualifying product	Add-on sold	Sales Value	Group Code
39843	158048	Bed 1	1	0	-£1,394	BE-MA-PS
39843	158048	Bed 1	1	0	£1,334	BE-MA-PS
39843	158048	Bed 1	1	0	£1,394	BE-MA-PS
39843	140562	Bed 2	1	0	-£1,896	BE-DB-PS
39843	140562	Bed 2	1	0	£1,825	BE-DB-PS
39843	140562	Bed 2	1	0	£1,896	BE-DB-PS
39843	130111	Pillow	0	-1	-£26	BE-BA-PI
39843	130111	Pillow	0	1	£26	BE-BA-PI
39843	130111	Pillow	0	1	£26	BE-BA-PI
40152	165256	Table	0	0	-£1,000	DI-TB-OK
40152	165256	Table	0	0	£950	DI-TB-OK
40152	165256	Table	0	0	£1,000	DI-TB-OK
40152	131999	Bed 2	1	0	-£1,896	BE-DB-PS
40152	131999	Bed 2	1	0	£1,825	BE-DB-PS
40152	131999	Bed 2	1	0	£1,896	BE-DB-PS
39999	175845	Table	0	0	-£1,000	DI-TB-OK
39999	175845	Table	0	0	£950	DI-TB-OK
39999	175845	Table	0	0	£1,000	DI-TB-OK
Desired output
Number of orders	3
Number of qualifying orders	2
Number of qualifying orders with add-on	1

I have tried all manner of measures to achieve this, without success.

Any suggestions or pointers on how to achieve the required output would be awesome!

Thanks in advance

[petejigsaw]

@johnt75Thanks for the follow-up answer on this query.

Your logic makes perfect sense, and performance is an issue given the size of my dataset.

I have come across a quirk using your original solution which I wondered if you could help overcome. In my original data, if the first order only had the below lines (because the add-on was entered on the order and then removed), how would I show that Add-on sales were ZERO?  Your

Order No.	Stock_ID	Item	Qualifying product	Add-on sold	Sales Value	Group Code
39843	158048	Bed 1	1	0	-£1,394	BE-MA-PS
39843	158048	Bed 1	1	0	£1,334	BE-MA-PS
39843	158048	Bed 1	1	0	£1,394	BE-MA-PS
39843	140562	Bed 2	1	0	-£1,896	BE-DB-PS
39843	140562	Bed 2	1	0	£1,825	BE-DB-PS
39843	140562	Bed 2	1	0	£1,896	BE-DB-PS
39843	130111	Pillow	0	-1	-£26	BE-BA-PI
39843	130111	Pillow	0	1	£26	BE-BA-PI

Your solution would still show 1, where it needs to show 0. A simple SUM doesn't work, as if there are multiple add-ons sold (and not cancelled), the total would be incorrect as it needs to show either 1 (where add-ons (any number) are sold) or 0 (where none are sold).

Thanks in advance for any assistance on this.

[johnt75]

You can try

Number of qualifying orders with addon = 
VAR SummaryTable = ADDCOLUMNS(
	SUMMARIZE( 'Table', 'Table'[Order No.], 'Table'[Item]),
	"@total", CALCULATE( SUM( 'Table'[Add-on sold]))
)
VAR Result = CALCULATE(
	DISTINCTCOUNT( 'Table'[Order No.]),
	FILTER( SummaryTable, [@total] > 1)
)
RETURN COALESCE( Result, 0 )

[petejigsaw]

@johnt75  Thank you - I had to make one change, as this line:

FILTER (SummaryTable, [@total] > 1)

needed to have > 0 at the end, rather than 1.

Much kudos for your help!

[johnt75 · Accepted solution]

You can use the below measures

Number of orders = DISTINCTCOUNT( 'Table'[Order no])

Number of qualifying orders =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Order no] ),
    'Table'[Qualifying product] = 1
)

Number of qualifying orders with addon =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Order no] ),
    'Table'[Add-on sold] = 1
)

[petejigsaw]

@johnt75Thank you - they work a treat.

Could I ask a follow-up question - currently I am using 'Add a conditional column' to create the [Qualifying Product] and [Add-on Sold] data. Is there a better way, avoiding this type of manipulation stage, to achieve the same output?

[johnt75]

That would depend on a few things. It would be possible in principle to use whatever logic determines your conditional column in the filter arguments to calculate but that would mean the processing time required would be used during report consumption, when the user can see it, rather than during data load.

Given that the columns will only be storing a very small number of distinct values the size of the columns should be really small, even for a large dataset, and it will likely give you better performance than doing the same calculation in DAX.

Another option might be to push the calculation up to the SQL stage so that the columns are returned from the base query and no further calculation would be required.