Displaying all series number and if there is duplicate display only with specific value

[Boopep]

Hi!

Requesting assistance in creating DAX measures because I want to display all my CLAIMSERIESNUMBER and its corresponding CLAIMS AMOUNT, but sometimes it has duplicate CLAIMSERIESNUMBER, if this happens i want to include only those CLAIMSERIESNUMBER with a value of "refiled" in RECEIVETYPE. 

Also i want to sum all the CLAIMS AMOUNT of all the output CLAIMSERIESNUMBER from the DAX above.

This is my table

[kushanNa]

Hi @Boopep 

are you looking for an output similar to this ? 

if so I have attached the sample pbix file i have created for your reference 

[Boopep]

Wow! i think you nailed it! Thank you so much! Amazing! 

[v-hjannapu]

Hi @Boopep,
If @kushanNa  answer has solved your issue, please Accept as solution. This will help other community members also when they face the same problem.

Regards,
Harshitha.

[danextian]

Hi @Boopep 

Several approaches:

• Clean your data source by removing duplicates which can be done in Power Query.
• If the above approach is not doable for some reason, you can create a calculated column that identifies which rows are to be included in the sum.
• Or you can just use a measure only approach. 

Which one to follow depends on what you want to do with the other rows that are not to be included in the sum.

Calculated column to  identify which rows to include:

Include = 
VAR _RowCount =
    COUNTROWS (
        FILTER (
            'DAX',
            'DAX'[ClaimSeriesNumber] = EARLIER ( 'DAX'[ClaimSeriesNumber] )
        )
    )
RETURN
    IF ( _RowCount > 1 && 'DAX'[ReceiveType] = "Refiled" || _RowCount = 1, TRUE (), FALSE () )

Measure only:

Total Claim = 
VAR _RowCount =
    CALCULATE ( COUNTROWS ( 'DAX' ), ALLEXCEPT ( 'DAX', 'DAX'[ClaimSeriesNumber] ) )
RETURN
    SUMX (
        FILTER (
            SUMMARIZECOLUMNS (
                'DAX'[ClaimSeriesNumber],
                'DAX'[ReceiveType],
                'DAX'[Claim Amount],
                "@Row Count", _RowCount
            ),
            [@Row Count] = 1
                || [ReceiveType] = "Refiled"
        ),
        [Claim Amount]
    )

Please see the attached pbix.

[MarcoSparkBI]

@Boopep ,hi ,basically it is not difficult to show what you want. you can use power query to identify how many records apprears of seriesnumber. for more than 1 times apprears , you can use select to filter only "refiled". 
recomend to provide some simple data  that i can provide more detail solutions

Regards
Marco

[Boopep]

ClaimSeriesNumber	FacilityName	ReceiveType	DateReceived	Claim Amount	Payable Amount	Status
1807091001589	BICOL MEDICAL CENTER	REFILED	06/25/2025	58800	NULL	RTH Transmittal
1807261001370	BULAN MEDICARE HOSPITAL	REFILED	07/03/2025	4200	NULL	RTH Transmittal
1812111000025	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	8000	NULL	RTH Transmittal
1812111000224	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	4340	NULL	RTH Transmittal
1812171000703	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	7000	NULL	RTH Transmittal
1902061000198	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	7000	NULL	RTH Transmittal
2502131002082	BICOL MEDICAL CENTER	NEW	02/13/2025	10738	NULL	RTH Transmittal
2502131002082	BICOL MEDICAL CENTER	REFILED	04/28/2025	10000	10000	Paid
2502131002083	BICOL MEDICAL CENTER	NEW	02/13/2025	10738	NULL	RTH Transmittal
2502131002083	BICOL MEDICAL CENTER	REFILED	04/28/2025	10000	10000	Paid
2502131002101	BICOL MEDICAL CENTER	NEW	02/13/2025	9464	NULL	RTH Transmittal
2502131002101	BICOL MEDICAL CENTER	REFILED	04/30/2025	9000	NULL	Denied Transmittal
2502131002102	BICOL MEDICAL CENTER	NEW	02/13/2025	7280	NULL	RTH Transmittal
2502131002102	BICOL MEDICAL CENTER	REFILED	04/30/2025	7280	7280	Paid

[Shahid12523]

DAX Measure to Sum Filtered Claims Amount

TotalFilteredClaimsAmount =
CALCULATE(
SUM('ClaimsTable'[CLAIMS AMOUNT]),
FILTER(
ALL('ClaimsTable'),
COUNTROWS(
FILTER(
ALL('ClaimsTable'),
'ClaimsTable'[CLAIMSERIESNUMBER] = EARLIER('ClaimsTable'[CLAIMSERIESNUMBER])
)
) = 1
|| 'ClaimsTable'[RECEIVETYPE] = "refiled"
)
)

This measure:
- Includes all unique CLAIMSERIESNUMBER
- For duplicates, only includes those with "refiled" in RECEIVETYPE
- Sums their CLAIMS AMOUNT

[Boopep]

[Boopep]

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[Boopep]

ClaimSeriesNumber	FacilityName	ReceiveType	DateReceived	Claim Amount	Payable Amount	Status
1807091001589	BICOL MEDICAL CENTER	REFILED	06/25/2025	58800	NULL	RTH Transmittal
1807261001370	BULAN MEDICARE HOSPITAL	REFILED	07/03/2025	4200	NULL	RTH Transmittal
1812111000025	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	8000	NULL	RTH Transmittal
1812111000224	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	4340	NULL	RTH Transmittal
1812171000703	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	7000	NULL	RTH Transmittal
1902061000198	RACELIS - TIONGSON MEDICAL CLINIC	REFILED	02/19/2025	7000	NULL	RTH Transmittal
2502131002082	BICOL MEDICAL CENTER	NEW	02/13/2025	10738	NULL	RTH Transmittal
2502131002082	BICOL MEDICAL CENTER	REFILED	04/28/2025	10000	10000	Paid
2502131002083	BICOL MEDICAL CENTER	NEW	02/13/2025	10738	NULL	RTH Transmittal
2502131002083	BICOL MEDICAL CENTER	REFILED	04/28/2025	10000	10000	Paid
2502131002101	BICOL MEDICAL CENTER	NEW	02/13/2025	9464	NULL	RTH Transmittal
2502131002101	BICOL MEDICAL CENTER	REFILED	04/30/2025	9000	NULL	Denied Transmittal
2502131002102	BICOL MEDICAL CENTER	NEW	02/13/2025	7280	NULL	RTH Transmittal
2502131002102	BICOL MEDICAL CENTER	REFILED	04/30/2025	7280	7280	Paid