topic Calculate Distinct count based off a sum greater than a number in DAX Commands and Tips

Calculate Distinct count based off a sum greater than a number

Anonymous — Wed, 20 Oct 2021 23:38:08 GMT

Hello

I have 2 tables a client table and a master table that are related. I want to calculate the number of clients thats $ is over 1M. I cant seem to get it, here are some example codes that dont work

CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]), MasterTable[$] > 1000000) This results in just the total number of clients in the data.

CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]), FILTER('MasterTable, CALCULATE(SUM('MASTERTABLE'[$]) >= 1000000))))

CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]), Filter(MasterTable, Calculate(sum(MasterTableJ[$]), allexcept(clienttable, masterclientname)) >= 1000000))

I even tried using a measure

IFMeasure = IF(SUM(MasterTable[$]) > 1000000, 1, 0)

Actual Measure = CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]),FILTER('MasterTable', 'IFMeasure'[1M] = 1))

Anyone have any examples/solution to this?

Re: Calculate Distinct count based off a sum greater than a number

Jihwan_Kim — Thu, 21 Oct 2021 03:04:46 GMT

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Clients count over 1600 : =
COUNTROWS ( FILTER ( Clients, [Sales :] > 1600 ) )

Re: Calculate Distinct count based off a sum greater than a number

Anonymous — Thu, 21 Oct 2021 15:51:12 GMT

My $ and client are on two different tables. Also the table with the $ is based off individuals not clients. The tables are related.