Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate Distinct count based off a sum greater than a number

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?

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

 

Picture1.png

 

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

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.