cancel
Showing results for
Did you mean:
Regular Visitor

## Sum & Count of DistinctCount depending on Groups

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

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

## Re: Sum & Count of DistinctCount depending on Groups

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

Anonymous
Not applicable

## Re: Sum & Count of DistinctCount depending on Groups

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors