Forum Discussion
calculate
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?
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 ) )
2 Replies
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 ) )Total Spend = SUM ( Table[Spend] ) Measure = CALCULATE(COUNTX(FILTER(VALUES(Table[Material]),[Total Spend]>=1000),[Total Spend]))
