Count Unique "Product Type" per Transaction

[rphang]

Hi everyone,

I am trying to find out the way to retrive unique counts of "Product Type" per transaction within my sales database.

My measure should return the results

-"2" counts for Transaction ID 100001

-"3" counts for Transaction ID 100002

-"1" counts for Transaction ID 100003

-"1" counts for Transaction ID 100004

Subsequently with the count calculated above, to display % vs total no. of transaction,

% of Transaction
1 Type	50%
2 Types	25%
3 Types	25%

My table structure is as such,

- Transaction Details that my POS system input

- Product database that stores Product Assortment

Table Name: Transaction Details

Transac ID	Store Product	Cost
100001	Apple	xx.xx
100001	Orange	xx.xx
100001	Bicycle	xx.xx
100002	Apple	xx.xx
100002	Bicycle	xx.xx
100002	Cola	xx.xx
100003	Orange	xx.xx
100003	Apple	xx.xx
100003	Pear	xx.xx
100003	Watermelon	xx.xx
100004	Orange	xx.xx
100004	Apple	xx.xx
100004	Pear	xx.xx
100004	Watermelon	xx.xx

Table Name: Products

Store Product	Product Type
Apple	Fruits
Orange	Fruits
Bicycle	Item
Cola	Drinks
Pear	Fruits
Watermelon	Fruits

--

Thank you

Ryan

[Anonymous]

If you slice by Transaction ID, this will give you the number of different product types per each transaction. If you have several transactions in scope, it'll tell you the same thing but in the context, that is, how many different product types there are in all the visible transactions.

// Products[Store Product] 1 -> * 'Transaction Details'[Store Product]

[Your Measure] =
COUNTROWS(
    SUMMARIZE(
        'Transaction Details',
        'Products'[Product Type]
    )
)

[rphang]

This is fantastic, with this measure it solves my first part.

Can I check what will be the measure to pull out seperately number of transaction with either count of 1, 2 or 3?

Cause with that I can do X-number of transaction / total transaction to get the % mix.