Count the number of unique entries(string) per category

[CSRiotech]

Hi All,

I have the following table structure:

Itemgroup	Itemno
A	1
A	2
A	2
B	3
B	3
B	3
C	4
C	5
C	6

And the output I need is a calculated column that contains the unique number of Itemno for each Itemgroup.
Like this:

Itemgroup	Itemno	Unique Entries of Itemno
A	1	2
A	2	2
A	2	2
B	3	1
B	3	1
B	3	1
C	4	3
C	5	3
C	6	3

I think the answer would be some sort of combination of SUMMARIZE/GROUPBY and DISTINCT COUNTROWS.  I just cannot figure this one out myself. 

Thanks in advance!

[v-yangliu-msft · Accepted solution]

Hi  @CSRiotech ,

Here are the steps you can follow：

1. Create calculated column.

Unique Entries of Itemno =
CALCULATE(
    DISTINCTCOUNT('Table'[Itemno]),
    FILTER(ALL('Table'),
    'Table'[Itemgroup]=EARLIER('Table'[Itemgroup])))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly