Create a Measure: Sum values if Paid Amount total is greater than $25,000

[AW1976NOVA]

Hi Power BI Community,

I'm trying to create a measure (to be used in a card visual) to SUM the total of a field if certain criteria are met.  I'm trying to create a measure that states:  If the 'Claim Data'[Plan Paid] amount is greater than $25,000, then include this value and sum these values together.

For example, here is a batch of dummy results/data:

In this case, Claim ID 1001, 2040, 1976 and 5501 should be included and summed while Claim ID 7001 should be ignored.  This is because 1001's total of ($57,302.84), 2040's total of ($33,911.00), 1976's total of 1976 (30,594.05) and 5501's total of $26,010.28 are all greater than $25,000.

As a result, the measure should come with a total of $147,818.17.

I attempted creating this but it is erroring out and I can not resolve it.  Please help.  Thank you.

CTest = var m=max('Claim Data'[Claim ID])

var b=SUMMARIZE('Claim Data','Claim Data'[Claim ID],"ClaimID", VALUES('Claim Data'[Claim ID]),"SUM Plan Paid",sum('Claim Data'[Paid]))

var c=CALCULATE(SUM([Claim ID]),FILTER(b,[SUM Plan Paid]>25000))

Return c

Thank you!

[amitchandak]

Try like

CTest = var m=max('Claim Data'[Claim ID])
var b= sumx(filter(SUMMARIZE('Claim Data','Claim Data'[Claim ID],"ClaimID", VALUES('Claim Data'[Claim ID]),"SUM Plan Paid",sum('Claim Data'[Paid])),
			[SUM Plan Paid]>25000),SUM([Claim ID]))
Return b

[AW1976NOVA]

Thank you for the quick response but I have an error from your formula

I'm not the best at trouble shooting within Power BI just yet.  It looks like the sum('Claim Data'[Paid]) portion of the formula is the culprit?  Could you assist?

[amitchandak · Accepted solution]

Try

//1
CTest = var m=max('Claim Data'[Claim ID])
var b= sumx(filter(SUMMARIZE('Claim Data','Claim Data'[Claim ID],"SUM Plan Paid",sum('Claim Data'[Paid])),
			[SUM Plan Paid]>25000),[SUM Plan Paid])
Return b
//2

CTest = var m=max('Claim Data'[Claim ID])
var b=SUMMARIZE('Claim Data','Claim Data'[Claim ID],"ClaimID", ('Claim Data'[Claim ID]),"SUM Plan Paid",sum('Claim Data'[Paid]))
var c=CALCULATE(sum([SUM Plan Paid]),values([Claim ID]),filter(b,[SUM Plan Paid]>25000))
Return c

[AW1976NOVA]

---

@amitchandak wrote:

Try

//1
CTest = var m=max('Claim Data'[Claim ID])
var b= sumx(filter(SUMMARIZE('Claim Data','Claim Data'[Claim ID],"SUM Plan Paid",sum('Claim Data'[Paid])),
			[SUM Plan Paid]>25000),[SUM Plan Paid])
Return b
//2

CTest = var m=max('Claim Data'[Claim ID])
var b=SUMMARIZE('Claim Data','Claim Data'[Claim ID],"ClaimID", ('Claim Data'[Claim ID]),"SUM Plan Paid",sum('Claim Data'[Paid]))
var c=CALCULATE(sum([SUM Plan Paid]),values([Claim ID]),filter(b,[SUM Plan Paid]>25000))
Return c

---

Thank you for working with me.  I really appreciate the help.

The formula is still erroring out and I've attached a screenshot.  For some reason it is not recongnizing the [SUM Plan Paid] or [Claim ID]:

I'm trying to wrap my head arounnd how this would work since we are referencing/naming our measure 'CTest' twice.  Could you help me understand what is going on here.  This is a bit over my head.

[amitchandak]

These are two versions of the formula . You have to use only one. Every formula end after returning a value