cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vyacheslavg Helper II
Helper II

Create column with distinct count on category level

Dear colleagues, 

 

Could you please advise how to create a column with distinct count on a category level.

 

The data is quite simple.

 

product idpart
1red
2green
2green
3blue
3black

 

The result should be 

 

product idpartdistinct count of parts on a product level
1red1
2green1
2green1
3blue2
3black2

 

I tried to play with 

Count of version total for product =
CALCULATE(DISTINCTCOUNT('Table1'[version]), ALL('Table1'[product]))
but it returns the value only on a row level.
 
It is very simple to create a matrix with this result, but I need these values in the full table with count on row level, because it will be a part of further calculation.
Both DAX or Power Query approaches will do, thanks a lot.
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Create column with distinct count on category level

Hi @vyacheslavg

 

Try this for your new calculated column (close to what you already had):

 

NewColumn=
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[version] ),
    ALLEXCEPT ( 'Table1', 'Table1'[product id] )
)

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Create column with distinct count on category level

Hi @vyacheslavg

 

Try this for your new calculated column (close to what you already had):

 

NewColumn=
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[version] ),
    ALLEXCEPT ( 'Table1', 'Table1'[product id] )
)

View solution in original post

vyacheslavg Helper II
Helper II

Re: Create column with distinct count on category level

Thanks for the solution, and even I was "close", yay. Smiley Happy

 

 

 

Super User IV
Super User IV

Re: Create column with distinct count on category level

Hi,

 

This calculated column formula works as well

 

=CALCULATE(DISTINCTCOUNT(Table1[part]),FILTER(Table1,Table1[product id]=EARLIER(Table1[product id])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors