Set flag if SUM of measure > 1000

[jd8766]

Hi, I have a table that looks like the below and I am connecting via SSAS tabular..

I have a measure that gets me the distinctcount of the contractID... however I somehow need to be able to filter this to only give me contracts that relate to a CompanyID that has a combined contractvalue of > 1000

So based o the above example, we know that CompanyID 1102 has a combined ContractValue of 1120.. so I somehow want to filter the below measure to only calculate where this clause is met.

Total Contracts : = count(ContractID)

I have tried the below with no luck - any ideas?

Flag =

if(SUMX (

VALUES ( Sales[CompanyID] ),

CALCULATE ( sum( Sales[ContractValue]) )

) > 1000,1,0)

Then I have tried

calculate([Total Contracts],filter(Sales,[Flag]=1))

[v-xiaotang · Accepted solution]

Hi @jd8766 

You can try this, create the measures below

SumPerCompanyID = SUMX(FILTER(ALL(Sales),Sales[CompanyID]=MIN(Sales[CompanyID])),Sales[ContractValue])

distinctCount = CALCULATE(DISTINCTCOUNT(Sales[ContractID]),FILTER(ALL(Sales),[SumPerCompanyID]>1000))

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[jsaunders_zero9]

Is it as simple as just putting a filter on the visual using the filter pane?

[jd8766]

No because it will be in a visual that will show other fields that shouldn't be filtered by this threshhold