Forum Discussion
How to exclude duplicate values
Check Measure and Measure2. File attached.
Hi,
Why should the answer be 110,500? Share data in a format that can be pasted in an MS Excel file.
Because the 2 I need removed are technically duplicates. Which would leave 22 units for $110,500 as the correct Totals. Thank you for your help!
Member Number Product Amount Booked FundedDate PCL Increase/PCL New 1110410 PERSONAL CREDIT LINE $9,500.00 29-Jul-24 New PCL 1239460 PERSONAL CREDIT LINE $10,000.00 22-Jul-24 New PCL 1472450 Personal Line $5,000.00 10-Jul-24 Existing PCL Increase 1596150 PERSONAL CREDIT LINE $14,000.00 11-Jul-24 New PCL 2316130 PERSONAL CREDIT LINE $7,000.00 11-Jul-24 New PCL 2588730 Personal Line $1,500.00 6-Jul-24 Existing PCL Increase 2588730 PERSONAL CREDIT LINE $1,500.00 6-Jul-24 New PCL 3215470 PERSONAL CREDIT LINE $4,500.00 31-Jul-24 New PCL 3574660 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 New PCL 3697240 PERSONAL CREDIT LINE $4,000.00 30-Jul-24 New PCL 4338890 PERSONAL CREDIT LINE $5,000.00 1-Jul-24 New PCL 4562300 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 New PCL 5563620 Personal Line $500.00 30-Jul-24 Existing PCL Increase 5563620 PERSONAL CREDIT LINE $500.00 30-Jul-24 New PCL 5648730 PERSONAL CREDIT LINE $10,000.00 23-Jul-24 New PCL 6546300 PERSONAL CREDIT LINE $1,000.00 18-Jul-24 New PCL 7412220 PERSONAL CREDIT LINE $10,000.00 29-Jul-24 New PCL 7536160 PERSONAL CREDIT LINE $2,500.00 2-Jul-24 New PCL 7898990 PERSONAL CREDIT LINE $2,000.00 24-Jul-24 New PCL 8523290 Personal Line $1,000.00 17-Jul-24 Existing PCL Increase 8979840 PERSONAL CREDIT LINE $500.00 30-Jul-24 New PCL 9514250 Personal Line $2,000.00 10-Jul-24 Existing PCL Increase 9632420 PERSONAL CREDIT LINE $10,000.00 31-Jul-24 New PCL 9873000 PERSONAL CREDIT LINE $500.00 24-Jul-24 New PCL Check Measure and Measure2. File attached.
Hi Ashish! When I input your calclulated column and those 2 measures I get a variance with New PCL. Would EARLIER not work since my real pbix has months/years worth of previous data? Should my measures relate to the monthly date table slicer somehow? I'm not sure where to go from here.
The Consider? calculated column is only counting/summing 8 of the Member Numbers for PERSONAL CREDIT LINE:
Product Amount Booked FundedDate FundedDateMonth PCL Increase/PCL New PCL Consider? Personal Line $5,000.00 10-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $1,000.00 17-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $2,000.00 10-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $1,500.00 6-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $500.00 30-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 PERSONAL CREDIT LINE $14,000.00 11-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $10,000.00 22-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $500.00 30-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $500.00 24-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $4,000.00 30-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $500.00 30-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $10,000.00 29-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $2,500.00 2-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $10,000.00 23-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $5,000.00 1-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $1,000.00 18-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $2,000.00 24-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $9,500.00 29-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $4,500.00 31-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $7,000.00 11-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $10,000.00 31-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $1,500.00 6-Jul-24 7/1/2024 0:00 New PCL 0
H! I have created 2 measures that get me the correct grand totals, but the row items are still incorrect.
The Existing PCL Increase is correct = 5 units for $10,000
The New PCL is incorrect, should = 17 units for $102,500
I have created a pbix for testing here : PCL Testing.pbix
If the SAME Member Number appears for both Existing and New, I need the measure to strip out the data rows pertaining to the New PCL redundant Member Number. This visual might make it clearer. Include all black and green(duplicate member number), exclude the red:
Member Number Product Amount Booked FundedDate PCL Increase/PCL New 1110410 PERSONAL CREDIT LINE $9,500.00 29-Jul-24 New PCL 1239460 PERSONAL CREDIT LINE $10,000.00 22-Jul-24 New PCL 1472450 Personal Line $5,000.00 10-Jul-24 Existing PCL Increase 1596150 PERSONAL CREDIT LINE $14,000.00 11-Jul-24 New PCL 2316130 PERSONAL CREDIT LINE $7,000.00 11-Jul-24 New PCL 2588730 Personal Line $1,500.00 6-Jul-24 Existing PCL Increase 2588730 PERSONAL CREDIT LINE $1,500.00 6-Jul-24 New PCL 3215470 PERSONAL CREDIT LINE $4,500.00 31-Jul-24 New PCL 3574660 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 New PCL 3697240 PERSONAL CREDIT LINE $4,000.00 30-Jul-24 New PCL 4338890 PERSONAL CREDIT LINE $5,000.00 1-Jul-24 New PCL 4562300 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 New PCL 5563620 Personal Line $500.00 30-Jul-24 Existing PCL Increase 5563620 PERSONAL CREDIT LINE $500.00 30-Jul-24 New PCL 5648730 PERSONAL CREDIT LINE $10,000.00 23-Jul-24 New PCL 6546300 PERSONAL CREDIT LINE $1,000.00 18-Jul-24 New PCL 7412220 PERSONAL CREDIT LINE $10,000.00 29-Jul-24 New PCL 7536160 PERSONAL CREDIT LINE $2,500.00 2-Jul-24 New PCL 7898990 PERSONAL CREDIT LINE $2,000.00 24-Jul-24 New PCL 8523290 Personal Line $1,000.00 17-Jul-24 Existing PCL Increase 8979840 PERSONAL CREDIT LINE $500.00 30-Jul-24 New PCL 9514250 Personal Line $2,000.00 10-Jul-24 Existing PCL Increase 9632420 PERSONAL CREDIT LINE $10,000.00 31-Jul-24 New PCL 9873000 PERSONAL CREDIT LINE $500.00 24-Jul-24 New PCL
