calculate

[caiying100126]

I have a table named "spend data"  with different Material numbers. For each material number, there are several spends.

I want to sum up the total spend for each material number and count how many distinct material numbers with its total spend greater than 100,000.

I created a new measure called "total spend per Material number"= calculate(sum('spend data'[totalspend]),allexcept('spend data','spend data'[material number])). And then created a measure called "number of material ID whose total spend >100,000"=calculate(distinctcount('spend data'[material number]),'spend data'[total spend per Material number]>100000).

But this functon does not work. The error shows " A function CALCULATE has been used in a TRUE/FALSE expression that is used as a table filter expression. This is not allowd.

What should I do to calculate the distinct Material numbers whose total spend greater than 100,000?

[amitchandak]

Total Spend = SUM ( Table[Spend] )
Measure = CALCULATE(COUNTX(FILTER(VALUES(Table[Material]),[Total Spend]>=1000),[Total Spend]))

[parry2k · Accepted solution]

@caiying100126 Add following measures 

following # Material over amount measure will give you the count.

Total Spend = SUM ( Table[Spend] )

# Material over amount = 
COUNTX ( VALUES ( Table[Material] ), IF ( [Total Spend] > 100000, 1 ) )