Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I wondered if someone could help me please. I have a measure below which calculates the sum of a field called Highest Estimate with various criteria for insurance claims references. As you will see from the table below, some records have multiple rows for the same Claim Ref calculate the SUM of one Highest Estimate not multiple.
How do I amend my measure to only sum one Highest Estimate as opposed to multiple
Thank you
@v-xinruzhu-msft @amitchandak @tamerj1 @danextian @goncalogeraldes
ClaimRef | NotificationDate | IncidentDate | Highest Estimate |
M18PL000150 | ######## | ######## | £50,000.00 |
M18PL000150 | ######## | ######## | £50,000.00 |
M20PL000100 | ######## | ######## | £74.00 |
M20PL000104 | ######## | ######## | £761.35 |
M20PL000111 | ######## | ######## | £90,000.00 |
M20PL000111 | ######## | ######## | £90,000.00 |
M20PL000112 | ######## | ######## | £6,743.00 |
M20PL000118 | ######## | ######## | £10,047.00 |
M20PL000133 | ######## | ######## | £600.00 |
M20PL000155 | ######## | ######## | £27,621.00 |
M20PL000159 | ######## | ######## | £10,000.00 |
M20PL000159 | ######## | ######## | £10,000.00 |
M20PL000159 | ######## | ######## | £10,000.00 |
M20PL000159 | ######## | ######## | £10,000.00 |
M20PL000159 | ######## | ######## | £10,000.00 |
M23PLI000001 | ######## | ######## | £99,999.00 |
Measure
Solved! Go to Solution.
Hi @spandy34
Try modifying this measure with the extra conditions:
Sum of Max Highest Estimate =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[ClaimRef] ),
"@Max",
CALCULATE (
MAX ( 'Table'[Highest Estimate] ),
ALLEXCEPT ( 'Table', 'Table'[ClaimRef] )
)
),
[@Max]
)
Proud to be a Super User!
Thanks for the replies from grazitti_sapna, OktayPamuk80, Kedar_Pande and danextian.
Hi @spandy34 ,
Have you solved your problem? If it is solved, please mark the replies that help you as solutions, it will be helpful for other members of the community who have similar problems as yours to solve it faster. If there are any more questions, please feel free to let us know. Thanks a lot!
Best Regards,
Zhu
Thanks you @grazitti_sapna @v-linhuizh-msft @Kedar_Pande . I really appreciate your time in responding but it didnt seem to give me the result I was expecting but the following measure from @danextian worked and I got the results I was expecting. Thank you
Hi @spandy34
Try modifying this measure with the extra conditions:
Sum of Max Highest Estimate =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[ClaimRef] ),
"@Max",
CALCULATE (
MAX ( 'Table'[Highest Estimate] ),
ALLEXCEPT ( 'Table', 'Table'[ClaimRef] )
)
),
[@Max]
)
Proud to be a Super User!
Thank you very much @danextian that has worked after adding the additional conditions as below. Thanks again for your help.
zz_11Performance Clinic Highest Estimate =
CALCULATE(
SUMX(
DISTINCT('INS_Main Claim Data'[ClaimRef]),
MAX('INS_Main Claim Data'[Highest Estimate])
),
'INS_Main Claim Data'[ClassOfBusinessCode] IN { "PL", "EL" } &&
NOT CONTAINSSTRING('INS_Main Claim Data'[PolicyCode], "LPL") &&
NOT CONTAINSSTRING('INS_Main Claim Data'[PolicyCode], "LEL") &&
'INS_Main Claim Data'[DBA_VW5_STATUS_FLAG_LOOKUP.Description] = "Closed" &&
'INS_Main Claim Data'[RepudiatedFlag] = TRUE
)
This measure should now sum only one Highest Estimate per ClaimRef and give you the correct total for your insurance claims!
If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
Hi,
In my opinion, it is better to prepare the data accordingly instead of having complex measures, which additionally might cause performance issues: As you have some duplicate records in your data, question is, can you remove these duplicates or do you need them (e.g. the notification and incident dates are different).
If you can't remove it, in power query duplicate this table, to have only ClaimRef and Highest Estimate in the table as distinct. In the modeling view, you can connect this new table "Higest Estimate" with the above table over ClaimRef.
By this, not only you don't have to deal with duplicates, but you get performance gains.
Regards,
Oktay
Hi @spandy34,
To ensure that only one "Highest Estimate" per unique Claim Reference is summed, I have incorporated the DISTINCT function alongside SUMX and MAX. Below is the revised measure:
zz_11Performance Clinic Highest Estimate =
CALCULATE(
SUMX(
DISTINCT('INS_Main Claim Data'[ClaimRef]),
MAX('INS_Main Claim Data'[Highest Estimate])
),
'INS_Main Claim Data'[ClassOfBusinessCode] IN { "PL","EL" } &&
NOT CONTAINSSTRING('INS_Main Claim Data'[PolicyCode], "LPL") &&
NOT CONTAINSSTRING('INS_Main Claim Data'[PolicyCode], "LEL") &&
'INS_Main Claim Data'[DBA_VW5_STATUS_FLAG_LOOKUP.Description] = "Closed" &&
'INS_Main Claim Data'[RepudiatedFlag] IN { TRUE }
)
This approach should resolve the issue by preventing duplicate sums for repeated claim references.
Thank You
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |