Forum Discussion
Support with Calculation evaluating several lines
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
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
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 !!!
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,
AngeliaHello,
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,