Count distinct by one group then sum as another group

[JuiceX]

Hi all,

Trying to find a solution to my issue.

I am trying to count the number of parts by a group but displayed a sum of another group but cant get the distinctcount straight in my mind. Example below

I want to count the number of unique parts in a quote, then display that as a sum for the month. If you note the last 3 lines are the same part, I want to count that part as one occurance for quote 1017P and one occurance for quote 1022P which would ultimatly sum to 2 for the month of Nov.

DATE	QUOTE_NO	TRIMMED_NUMBER
1/11/2018	1017P	CPGVE-04011
1/11/2018	1017P	CPGVE-04011
1/11/2018	1017P	CPGVE-04110
1/11/2018	1017P	CPGVE-07011
1/11/2018	1017P	CPGVE-10010RH
1/11/2018	1017P	CPGVE-10010RH
1/11/2018	1017P	CPGVE-20010
1/11/2018	1017P	CPGVE-21031LH
1/11/2018	1017P	CPTTH-04020
1/11/2018	1022P	CPTTH-04020
1/11/2018	1022P	CPTTH-04020

Expected output

Month	Unique Parts Per Quote
Nov-18	8

Thanks kindly

[affan · Accepted solution]

Hi @JuiceX,

You can use the below measure to get the desired result. Replace 'Table1' with your table name.

Measure = CALCULATE(COUNTX(SUMMARIZE(Table1,Table1[QUOTE_NO],Table1[TRIMMED_NUMBER]),CALCULATE(VALUES(Table1[TRIMMED_NUMBER]))))

I have tested the same and I got the below result

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[JuiceX]

Excellent, works, thankyou!!