SUMX with COUNT returns incorrect value

[PS_78]

Hello All - I have issue with a measure created to calculate count using SUMX function. My requirement is to calculate count of document #'s where type = "TP1". But the measure I created returns incorrect values. It seems to be multiplication of Count (Sum of Count where type = TP1) with total count (Count of Doc #). In additional I also need to calculate distinct count for doc #, CC and Year combination. Please help check this. I am also giving a link of PBIX file here. SUMX Count Issue.zip 

Formula in the measure = 

Msr_Count = SUMX(FILTER('Table', 'Table'[Type] = "TP1"), COUNT('Table'[Doc #]))

 

Thanks,

Phani

[lbendlin · Accepted solution]

 In additional I also need to calculate distinct count for doc #, CC and Year combination. 

DC = COUNTROWS(summarize('Table',[CC],[Doc #],[Year]))

[PS_78]

Hi @lbendlin - Awesome. Thanks. But what is the issue with COUNT in SUMX? Also how can I get distinct count of rows for the combination of Doc #, Type and CC for the same document type?

Thanks,

@PS_78 

[lbendlin]

You can use a table visual for that

When you use the count inside the SUMX you are multiplying each row's filtered count with each row's unfiltered count.

If you need to use SUMX, use 

Msr_Count2 = SUMX(FILTER('Table', [Type] = "TP1"), 1)