Total incorrect

[MEFOX]

I'm using the table visual. I've turned on Totals in the Format section and I get a Total that is incorrect. The Total should be 584. Can someone give me some ideas of what might cause this. Is there another way to get the total at the bottom of a table? 

Registered = DISTINCTCOUNTNOBLANK('CRM FactEvents'[ContactID])

ContactID is a column in the CRM FactEvents table.

Thanks for the help.

[Anonymous]

Hi @MEFOX 

Please try this formula :

Measure = CALCULATE(COUNT('CRM FactEvents'[ContactID]),FILTER('CRM FactEvents','CRM FactEvents'[ContactID]<>BLANK()))

Best Regard

Community Support Team _ Ailsa Tao

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

[jdbuchanan71]

@MEFOX 

You need to use something other than DISTINCTCOUNT because it is giving you the correct answer, the same Contact ID is in several days so in the total each DISTCINT countact id only gets counted once .  You should us COUNTROWS.

[MEFOX]

Unfortunately I'm not having much luck with the COUNTROWS function. Are you aware of another visual (maybe a marketplace visual) that might sum the rows in the visual instead of the data behind the visual?

[jdbuchanan71]

@MEFOX That total just means that some [ContactID] are in more than 1 [Type] so in the total they only get counted once (DISTINCTCOUNTNOBLANK).  Try it with a SUMX over the [Type].

Registered =
SUMX (
    VALUES ( 'CRM FactEvents'[Type] ),
    CALCULATE ( DISTINCTCOUNTNOBLANK ( 'CRM FactEvents'[ContactID] ) )
)

[MEFOX]

Your solution works when there is just Type and Registered in the table but when I add the date column it no longer provides the correct Total.

[MEFOX]

This seems to work for the Type column but I need to add additional columns from another table to the Values clause. I can't seem to get the syntax for that correct. Are you able to provide the syntax for that ?