Forum Discussion

PS_78's avatar
PS_78
Helper II
2 years ago
Solved

SUMX with COUNT returns incorrect value

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

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

     

     In additional I also need to calculate distinct count for doc #, CC and Year combination.
    DC = COUNTROWS(summarize('Table',[CC],[Doc #],[Year]))

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

     

     In additional I also need to calculate distinct count for doc #, CC and Year combination.
    DC = COUNTROWS(summarize('Table',[CC],[Doc #],[Year]))
    • PS_78's avatar
      PS_78
      Helper II
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago

        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)