Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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]:
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |