Sum of Distinct Counts

[chq]

I need to be able to total each of these distinct counts, not just see the number of distinct counts. 

Essentially I want a total of each rows distinct count. Can that be done? I had to scrub the data so below is what the table looks like in Power Bi, followed by the table where the data is stored. 


I just want to be able to sum each rows distinct count....

Any ideas? Thanks!
This is What Power Bi is giving meThis is what the data is based on

[parry2k · Accepted solution]

@chq try following measure

# Count = SUMX( VALUES( 'Table'[Event Title] )  ,CALCULATE(DISTINCTCOUNT( 'Table'[ID#] ) ))

[chq]

Thanks that looks like it works!

If I wanted to apply a filter for a year to the measure what would I need to add? I tried this but it does not appear to work.

SUMX(FILTER('Table',[Date]=2019), VALUES( 'Table'[Event Title] ) ,CALCULATE(DISTINCTCOUNT( 'Table'[ID#])))

[parry2k]

@chq try this

# Count = SUMX( ALLSELECTED( 'Table'[Event Title] )  ,CALCULATE(DISTINCTCOUNT( 'Table'[ID#] ) ))

[chq]

That gives me the same number as before.

I need to be able to compare the sums of two different years using the original formula you provided. Is there any way to apply a filter to the equation to achieve this? Thanks

[Cmcmahan]

There's no need to change the equation.  PowerBI measures work amazingly with the current context of a visual.  

If you create a table and add years to it, and then add this measure, it will calculate the measure for each year for you!  If you add other categories, it will work for each subgrouping of rows.  

[chq]

How would I be able to calculate the difference between the two years though? I need to be able to show percent change and the total count difference.

[Cmcmahan]

If you've only got 2 years of data you want to compare at a time, then this solution should work for you:

https://community.powerbi.com/t5/Desktop/Difference-between-two-columns-or-rows/td-p/188185

[chq]

It actually contains up to five years at any given time.