DISTINCTCOUNT doesn't give correct result as a column

[gspollock]

Hi all,

I want to create a column that takes a distinct count of account_id column for each Account Manager, like this:

Number Accounts Shared =  CALCULATE(DISTINCTCOUNT(mbammsreportshare[account_id])) 

The column is simply returning the total count for that Account Manager.  The same formula in a measure gives the correct results.  

Account Manager	Number Accounts Shared (measure)	Number Accounts Shared (column)
Acct Mgr1	1	1
Acct Mgr2
Acct Mgr3	2	3
Acct Mgr4
Acct Mgr5	4	4
Acct Mgr6	1	1
Acct Mgr7	1	1
Acct Mgr8	4	4
Acct Mgr9	6	15

I need to use a column because I want to create a measure that sums Number Accounts Shared like this:

Total Over 3 = CALCULATE(sum( mbammsreportshare[Number Accounts Shared]), FILTER(mbammsreportshare,[Number Accounts Shared]>3 ))

Power BI won't allow a measure in the SUM expression. 

A second problem is that summing the column returns blanks when using the filter expression.  

If there is a way around using a column for Number Accounts Shared, I would still like to know why does it not work as a column.

Thanks for your help!

[tamerj1 · Accepted solution]

Hi @gspollock 

Please vote for https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

and try

Total Over 3 =
SUMX (
VALUES ( mbammsreportshare[Account Manager] ),
INT ( [Number Accounts Shared] > 3 )
)

[Greg_Deckler]

@gspollock You can get a total for your measure doing this. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8