Forum Discussion
Count distinct value
Hello!
I have a database where for each accounts there is an employee. There is the case where one or more employee have the same accounts, so the accounts name is duplicated in database. Each Accounts have a market share : A,B,C and I need to count how many acccounts with C there are in my Database related to total of the accounts. The problem is that the formula that I'm using counts the same account several time, for each employee. How can I count just one time the accounts?
In the following the formula that I'm using:
% Tier C = CALCULATE ( COUNT( Account [Code]) , Filter('Account', 'Account'[TIER] = "C")) / CALCULATE ( COUNT( Account [Code]) , Filter('Account', 'Account'[TIER] = "A" || 'Account [Code]) , Filter('Account', 'Account'[TIER] = "B"|| 'Account [Code]) , Filter('Account', 'Account'[TIER] = "C"))
Many thanks!
If account code is duplicate, please use DISTINCTCOUNT
Count Tier C = CALCULATE ( DISTINCTCOUNT( Account [Code]) , Filter('Account', 'Account'[TIER] = "C"))
Total Count = DISTINCTCOUNT( Account [Code])
% Tier C = Count Tier C/Total Count
Let me know, it works or not
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos
1 Reply
If account code is duplicate, please use DISTINCTCOUNT
Count Tier C = CALCULATE ( DISTINCTCOUNT( Account [Code]) , Filter('Account', 'Account'[TIER] = "C"))
Total Count = DISTINCTCOUNT( Account [Code])
% Tier C = Count Tier C/Total Count
Let me know, it works or not
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos