In Power BI - count and sum instances in transactional database where item A is more frequent than item B

Question

I have a transactional table that looks like data that would flow in from a point of sale. Using DAX/Power BI, I want to be able to count and sum the instances where Item A is more frequent on the invoice than Item B.

I'm having a hard time pasting my data but imagine a transactional dataset with 3 Columns: Invoice Number, Qty sold, Product

Invoice | Qty sold | Product 
---------------------------------
1111    | 5        | Apples
1111    | 6        | Bananas
1111    | 6        | Oranges
1112    | 10       | Apples
1112    | 5        | Bananas
1112    | 3        | Oranges
1112    | 3        | Strawberries

And I want to be able to see it every combination of products above and how frequently 1 has a greater sales quantity than the other:

Apples > Apples: 0
Apples > Bananas: 1
Apples > Oranges: 2
Apples > Strawberries: 1
Bananas > Bananas: 0
Bananas > Apples: 1
Bananas > Oranges: 2
etc

I have tried duplicating the table and doing a many to many join on invoice. From there, count rows from table 1 where count > than table 2 using the "related" and "related table" functions (this is how I would do it in SQL). This hasn't worked due to the many to many nature. I have also tried something like this but it is not providing the desired output:

MoreFreq = 
CALCULATE(
    COUNT(Fact2[Qty Sold]),
    FILTER(
        Fact2,
        Fact2[Qty Sold] > Fact1[Qty Sold]
    )
)

Any help would be appreciated. Thanks!

Answer 1

Since you only provided a single table, not really sure how you plan to display it visually, with more Dimensions to provide selection you will be able to do something more flexible.

You could create a Calculated Table:

Table = 
ADDCOLUMNS(
    DISTINCT(GENERATE(VALUES(Fact2[Product]);SELECTCOLUMNS(VALUES(Fact2[Product]);"Product2";[Product])));
    "Count";
            COUNTX(VALUES(Fact2[Invoice]);
                VAR p1 = [Product]
                VAR p2 = [Product2]
                VAR p1c = CALCULATE(SUM(Fact2[Qty]);p1=Fact2[Product])
                VAR p2c = CALCULATE(SUM(Fact2[Qty]);p2=Fact2[Product])
                RETURN
                    IF(p1c>0 && p2c>0 && p1c>p2c;1)
    )
)

Answer 2

I took a crack at it, most of the complexity stems from the fact that you want to add the product combination (e.g. "Apples > Bananas") to visual in Power BI. In order to do this it needs to be a physical column in your data model. That eliminates create a single measure. I chose to use a calculated table. In this table I calculate all the product combinations and their related quantity per invoice and add a new display column "product combination":

Crossjoin = 
FILTER (
    ADDCOLUMNS (
        CROSSJOIN (
            'Table',
            SELECTCOLUMNS ( VALUES('Table'[Product]), "ProductName",  'Table'[Product] )
        ),
        "RelatedQty", CALCULATE (
            SUM ( 'Table'[Qty sold] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Invoice] ),
                'Table'[Product] = EARLIER ( [ProductName] )
            )
        ),
        "Product Combination", 'Table'[Product] & " > " & [ProductName]
    ),
    'Table'[Product] <> [ProductName]
        && [RelatedQty] > 0
)

After that the measure to calculate the instances where a product is sold more than another is pretty straightforward:

Measure = 
SUMX (
    'Crossjoin',
    IF ( 'Crossjoin'[Qty sold] > 'Crossjoin'[RelatedQty], 1, 0 )
)

An alternative solution could be to generate all the "Product Combinations" possible from the data at Data Load as a new table and do the crossjoin inside the measure. I chose the calculated table to make the calculations more visible.

Hope that helps!

Jan

Answer 3

You can do this with two copies of a product dimension, and no relationships to the fact table.

'Fact' is as you shared in your original post.

'Dim1' and 'Dim2' are each a distinct list of all products.

Expected usage is that you put 'Dim1'[Product1] and 'Dim2'[Product2] onto a table or matrix visual. For matrix, you could do one on rows and one on columns.

// base measure
Quantity = SUM ( 'Fact'[Qty Sold] )

// the measure you want
Dim1 > Dim2 =
// Count the rows defined by the FILTER table expression.
// This will be a count of distinct invoices, based on a
// filter predicate.
COUNTROWS (
    // Iterate invoices, keeping only those that have Dim1 > Dim2
    FILTER (
        VALUES ( 'Fact'[Invoice] ),
        // Fore each invoice, calculate Dim1Qty and Dim2Qty
        VAR Dim1Qty =
            CALCULATE (
                [Quantity],
                TREATAS ( VALUES ( 'Dim1'[Product1] ), 'Fact'[Product] )
            )
        VAR Dim2Qty =
            CALCULATE (
                [Quantity],
                TREATAS ( VALUES ( 'Dim2'[Product2] ), 'Fact'[Product] )
            )
        RETURN
            // Keep only invoices where this predicate is true
            Dim1Qty > Dim2Qty
    )
)