Sum & Count of DistinctCount depending on Groups

[PeterL1]

Hi, 

There is probably a question for this, but cannot find one.

I need PowerBi to calculate a sum of total transaction, as well as a count for transactions within a grouped product group.

I've got the following data (i've done group for the product groups):

TransactionID	Product	Product Group	GROUP (NOT IN DATA)
1	large bike	LARGE BIKES	BIKES
1	small bike	SMALL BIKES	BIKES
1	hardcover book	HARDCOVER BOOKS	BOOKS
1	paperback book	PAPERBACK BOOKS	BOOKS
1	plate	PLATES	CROCKERY
2	bowl	BOWLS	CROCKERY
3	plate	PLATES	CROCKERY
3	hardcover book	HARDCOVER BOOKS	BOOKS
4	bike	BIKES	BIKES
4	large bike	LARGE BIKES	BIKES
4	small bike	SMALL BIKES	BIKES
4	hardcover book	HARDCOVER BOOKS	BOOKS

What i have done is to concatenate the TransactionID and the Product Group (PRODTRANSID), and done a measure =DISTINCTCOUNT (data1[PRODTRANSID]) which will give me the counted transactions for each product group. (dont know if this is correct but shows the right numbers)

What i cant figure out is how to make it look like the below results (depending on how many transactions are done in each product group.

I want a transaction to be 1 if you have sold a large bike and a small bike in the same transaction.

i've tried =CALCULATE(DISTINCTCOUNT(data1[PRODTRANSID]),data1[GROUP (NOT IN DATA)]) but just sums up the transactions.

GROUP	All Product Groups (SUM)	Transaction Count By Product Group
BIKES	4	2
BOOKS	4	3
CROCKERY	4	3
PLANT	4	1

Note that i have over 60 million lines of data and i have more than 200 product groups.

Thanks 
Peter

[Anonymous · Accepted solution]

Why not just use simple DISTINCTCOUNTs?

All Product Groups (SUM) = DISTINCTCOUNT(data1[Product Group])

Transaction Count By Product Group = DISTINCTCOUNT(data1[TransactionID])

If you then put a table with GROUP (NOT IN DATA) column (renamed to GROUP) and two measures shown above for the data you provided the result would be like:

Sample result

Note: you may want to hide Total row as it also calculates distinct counts of groups and transactions.

Hope that helps.

Cheers,

Pawel