Calculating SUM of first record

[spandy34]

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

zz_11Performance Clinic Highest Estimate = CALCULATE(SUM('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 })

[v-linhuizh-msft]

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

[spandy34]

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

zz_11Performance Clinic Highest Estimate = CALCULATE(

SUMX (

    ADDCOLUMNS (

        SUMMARIZE ( 'INS_Main Claim Data', 'INS_Main Claim Data'[ClaimRef] ),

        "@Max",

            CALCULATE (

                MAX ( 'INS_Main Claim Data'[Highest Estimate] ),

                ALLEXCEPT ( 'INS_Main Claim Data', 'INS_Main Claim Data'[ClaimRef] )

            )

    ),

    [@Max]

),

'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

)

[danextian · Accepted 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]
)

[spandy34]

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 (

    ADDCOLUMNS (

        SUMMARIZE ( 'INS_Main Claim Data', 'INS_Main Claim Data'[ClaimRef] ),

        "@Max",

            CALCULATE (

                MAX ( 'INS_Main Claim Data'[Highest Estimate] ),

                ALLEXCEPT ( 'INS_Main Claim Data', 'INS_Main Claim Data'[ClaimRef] )

            )

    ),

    [@Max]

),

'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

)

[Kedar_Pande]

@spandy34 ,

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

www.linkedin.com/in/kedar-pande

[OktayPamuk80]

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

[grazitti_sapna]

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