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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AW1976NOVA
Post Patron
Post Patron

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

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:

11111.PNG

 

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.

222.PNG
 
 
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!
1 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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

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

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

 

777.PNG

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?

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


@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]:

 

76.PNG

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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