Forum Discussion

rivastoday's avatar
rivastoday
Frequent Visitor
8 years ago
Solved

Support with Calculation evaluating several lines

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

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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


4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      rivastoday
      Frequent Visitor
      8 years ago

      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 NumberAction Code Compensation 
      448H6Q36 $                         -  
      448H6Q1 $                    8.36
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      448H6Q1 $                         -  
      457LVW36 $                    8.36
      457LXP1 $                    8.36
      457LXP1 $                         -  
      457LXP1 $                         -  
      457LXP36 $                         -  
      457LXP1 $                         -  
      45906V1 $                         -  
      45906V1 $                         -  
      45906V36 $                         -  
      45906V37 $                  10.00
      45906V5 $                         -  
      4590715 $                  12.00
      4590716 $                         -  
      4590717 $                         -  
      45907914 $                    8.00
      45906336 $                    8.36

       

      Thanks Again !!!

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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