Distinct count with a measure as a filter, within the measure!

[Anonymous]

Probably quite basic and trivial., but i am basically trying to do a count of distinct account numbers, where the total revenue is <£10000

CstmrGrp = CALCULATE(DISTINCTCOUNT('Customer Group Accounts'[Group Name]), H12015[FinalAmountCalc]<10000)

FinalAmountCalc = Calculate(Sum(H12015[Final Amount (£)]))

maybe im just approaching this incorrectly but any help would be appreciated!

[v-juanli-msft · Accepted solution]

Hi @Anonymous

It seems [Group Name] in table 'Customer Group Accounts' has duplicates, and 'Customer Group Accounts' has a realtionship with table 'H12015'. 

Based on my test, i create a related calculated column in the table 'Customer Group Accounts' from table 'H12015'.

related = RELATED(H12015[Final Amount (£)]) 

flag = IF(MAX([related])<10000&&MAX([related])>0,1,0)

discount = CALCULATE(DISTINCTCOUNT('Customer Group Accounts'[Group Name]),FILTER(ALL('Customer Group Accounts'),[flag]=1))

Best regards

Maggie

[Anonymous]

hi @v-juanli-msft

Group Name is a distinct table it is used as a unique key for H12015 which is the file with all the transactions and i am able to convert the account names in the H12015 file to distinct group names using the group name table.

thanks for helping Maggie!

[v-juanli-msft]

Hi @Anonymous

Since Group Name is a unique key, my previous formula also so help, or just replace "DISCOUNT" with "COUNT", does my reply slove your problem, if so, could you accept it as a solution so that others may refer to it.

Best Regards

Maggie

[Zubair_Muhammad]

Hi
Try with

CstmrGrp = Countrows(filter(values('Customer Group Accounts'[Group Name]),[FinalAmountCalc]<10000))

[jenasan]

my hero