CountX or something more?

[one3nine0]

I have a table of sales data which includes invoice number, item number, quantity, and $ amount.

I want to show the count of the number of times two products appear in the same invoice.

For instance, when I create a pivot table, i want to have a measure that will show the count of number of times Item A and B in the same invoice. From the table below, Invoice 2, 3, and 4 would show a count of 1 (each invoice had product A and B) in it, and invoice 1 would have 0.

Invoice #	Item	Quantity	$ Amount
1	A	1	$400
1	C	1	$200
2	A	1	$400
2	B	2	$500
3	A	1	$400
3	B	1	$250
4	A	2	$800
4	B	4	$1000
4	C	6	$1200

<tbody>
</tbody>

For bonus points: the main goal is to identify which invoices have items A and B in it, and then swap out item B for another item and price to find out new invoice total. The idea is to find out what the total selling point would be if one item was upgraded to another, and to be able to visually see that in a pivot table comparison

 

[Ozeroth]

Hi there,

Your first question can be answered with a "Basket Analysis" pattern. See here on DAX Patterns.

You basically add a 'Filter Item' table to your data model with an inactive relationship to your Sales table, and set up a measure that looks like this:

=
CALCULATE (
    DISTINCTCOUNT ( Sales[Invoice #] ),
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[Invoice #] ),
        ALL ( Sales[Item] ),
        USERELATIONSHIP ( Sales[Item], 'Filter Item'[Filter Item] )
    )
)

Sample PowerPivot model using your data here.

On your second question, I'm sure that can be done somehow, but would need to understand the full logic about which item is replaced with which to offer any suggestions.

Best regards,
Owen