Support with Calculation evaluating several lines

[rivastoday]

Hello team I need support with the next:

I need to calculate the distint count of claims, the logic is sum AC1 + AC5 + AC14 with compensation, the formula is the next:

Reparability = CALCULATE([Net_Claims],LAM_Data[GSP Labor Price]>0,LAM_Data[Action Code]=1||LAM_Data[Action Code]=5||LAM_Data[Action Code]=14||LAM_Data[Action Code]=36)

The trouble is the AC36 sometimes is in combination with the AC1 for the same claim, and the compensation is in the AC1, so if I just make the sum of AC36 I would be duplicating claims in my formula. Every claim can have several action codes which means several lines, so what I need is the count of AC36 where the AC1 is not compensated (evaluating several lines for the same claim number)

Thanks a Lot for the Support

[v-huizhn-msft]

Hi @rivastoday,

After research and preview, I am still confusing about your requirement. Please share your sample data and list expected result. Do hide sensitve information before uploading your data. Or you can create fake data with similar structure. So that we can post detailed solution.

Thanks,
Angelia

[rivastoday]

Hello Angelia,

Thanks a lot for reply, let me share a better example:

• I have a column with claim number which can be in many rows depending the action codes contained in the claim (Column Action Code) but only 1 is compensated (Column Compensation)
• For my formula I need to calculate the distint count of the Claim Number, I need to know how many real claims I have, and then how many compensated with AC1, AC5 and AC14.
• Additional to my formula I need to add how many AC36 I have, except if the claim with AC36 is in combination with AC1 in the same claim and the AC1 is compensated to avoid duplicity.
• The logic is the action code with the highest labor is the one compensated but only one by claim. To measure reparability I need to consider AC1, AC5, AC14 and AC36, but if the AC36 is in combination with AC1 this has priority

Claim Number	Action Code	Compensation
448H6Q	36	$                         -
448H6Q	1	$                    8.36
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
457LVW	36	$                    8.36
457LXP	1	$                    8.36
457LXP	1	$                         -
457LXP	1	$                         -
457LXP	36	$                         -
457LXP	1	$                         -
45906V	1	$                         -
45906V	1	$                         -
45906V	36	$                         -
45906V	37	$                  10.00
45906V	5	$                         -
459071	5	$                  12.00
459071	6	$                         -
459071	7	$                         -
459079	14	$                    8.00
459063	36	$                    8.36

Thanks Again !!!

[v-huizhn-msft · Accepted solution]

Hi @rivastoday,

>>For my formula I need to calculate the distint count of the Claim Number,

You can use the formula below to calculate the distint count of the Claim Number

count=DISTINCTCOUNT(LAM_Data[Claim Number])

>>I need to know how many real claims I have, and then how many compensated with AC1, AC5 and AC14. Additional to my formula I need to add how many AC36 I have, except if the claim with AC36 is in combination with AC1 in the same claim and the AC1 is compensated to avoid duplicity.

Please calculate using the formula below.

Reparability =
CALCULATE (
    DISTINCTCOUNT ( LAM_Data[Claim Number] ),
    FILTER ( LAM_Data, LAM_Data[Compensation ] > 0 || LAM_Data[Action Code] = 1 )
)
    + CALCULATE (
        DISTINCTCOUNT ( LAM_Data[Claim Number] ),
        FILTER ( LAM_Data, LAM_Data[Compensation ] > 0 || LAM_Data[Action Code] = 5 )
    )
    + CALCULATE (
        DISTINCTCOUNT ( LAM_Data[Claim Number] ),
        FILTER ( LAM_Data, LAM_Data[Compensation ] > 0 || LAM_Data[Action Code] = 14 )
    )
    + CALCULATE (
        DISTINCTCOUNT ( LAM_Data[Claim Number] ),
        FILTER (
            LAM_Data,
            LAM_Data[Action Code] = 36
                || LAM_Data[Compensation ] = BLANK
        )
    )

Best Regards,
Angelia

[rivastoday]

Hello,

I tried the formula and still getting duplicated claims, in the next example the formula considered the AC36 with compensation blank or "0" and also the AC1 compensated, so its counting both. why I need is a formula to evaluate the claim number in different rows and identify if the AC1 is compensated to exclude this combination of AC1+ AC36 from the calculation

Claim Number	Action Code	Compensation
448H6Q	36	$                         -
448H6Q	1	$                    8.36
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -
448H6Q	1	$                         -

Thanks,