Sum Distinct within PivotTable?

[TheMacroNoob]

Hello all,

I am trying to use PivotTables to summarize and filter data. I have various tables that all do what I want, except for one.
I have a list of RMs (People), their Units Under Management, and Total Claim Cost per RM. Some properties have multiple claims, thus repeating data such as Unit count.
(A property with 100 units might have 3 claims, making the PivotTable display 300 units.)

Is there any way to make a PivotTable sumdistinct, or something similar, to display the RM, their units under management (counting the units for a property only once), and their total cost?

Please advise if you know the solution off the top of your head. Thank you very much.

Problem Example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Data				Problem				Desired Result (Reality)				Comparison
2	Property	Max of Units	Sum of Incurred Total		RM	Sum of Units	Sum of Incurred Total		RM	Units	Sum of Incurred Total			Ben (wrong)	Ben (correct)
3	100	250	$150,000.00		Amy	650	$662,407.90		Amy	350	$662,407.90		Units	1065	515
4	101	500	$180,459.00		Ben	1065	$5,052,489.80		Ben	515	$5,052,489.80
5	102	100	$19,795.00		Clarissa	200	$647,976.00		Clarissa	200	$647,976.00
6	103	300	$49,594.00		Daryn	20	$826,395.80		Daryn	20	$826,395.80
7	104	400	$683,140.00		Jamie	125	$643,325.60		Jamie	125	$643,325.60
8	105	200	$647,976.00		Jason	650	$1,162,045.90		Jason	325	$1,162,045.90
9	106	50	$665,044.00		John	100	$19,795.00		John	100	$19,795.00
10	107	75	$1,012,045.90		Josh	1000	$180,459.00		Josh	500	$180,459.00
11	108	100	$1,134,092.70		Phil	175	$673,837.30		Phil	175	$673,837.30
12	109	50	$612,813.90		Sharlene	100	$665,044.00		Sharlene	50	$665,044.00
13	110	125	$643,325.60		Ted	1200	$2,427,466.70		Ted	600	$2,427,466.70
14	111	175	$673,837.30		Grand Total	5285	12961243			2960	$12,961,243.00
15	112	150	$3,000,466.20		This is a problem because the				This desired result shows the RM and their
16	113	20	$826,395.80		PivotTable is summing the units				total units under management, without
17	114	200	$1,744,326.70		summing the units, adding the				duplicating properties by claims
18	115	265	$917,930.90		same property values however
19	Grand Total	500	$12,961,243.00		many times there are claims				I would like it to sum units per property
20					(in that property)				Sum Distinct units based on ID
PivotTables

Cell Formulas
Range	Formula
J3	J3	=DATA!R8+DATA!R19
K3:K13	K3	=SUMIF(DATA!$H:$H,PivotTables!I3,DATA!$S:$S)
J4	J4	=DATA!R17+DATA!R22+DATA!R29
J5	J5	=DATA!R12
J6	J6	=DATA!R26
J7	J7	=DATA!R20
J8	J8	=DATA!R3+DATA!R15
J9	J9	=DATA!R7
J10	J10	=DATA!R5
J11	J11	=DATA!R21
J12	J12	=DATA!R13
J13	J13	=DATA!R10+DATA!R27
J14:K14	J14	=SUM(J3:J13)
N3	N3	=GETPIVOTDATA("Units",$E$2,"RM","Ben")
O3	O3	=J4

 

[TheMacroNoob]

I forgot to post my data. Apologies.

Problem Example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	Claim Number	ID	Name	Combined Name	State	City	Crime Level	RM	RMM	Year Built or Rehab	Year	Year Category	Building Age	Loss Date	Cause Category	Cause	Detail Cause	Units	Incurred Total	Paid Total	Outstanding Total	Incurred per Door
3	1	100	Potato	100 - Potato	ME	Augusta	High	Jason	Rebecca	Year Built	2002	11-20	20	11/14/2019	Wind	Wind - Damage	Wind - Damage Onsite	250	$100,000	$10,000	$90,000	$400
4	1	100	Potato	100 - Potato	ME	Augusta	High	Jason	Rebecca	Year Built	2002	11-20	20	5/14/2020	Wind	Wind - Damage	Wind - Damage Onsite	250	$50,000	$25,000	$25,000	$200
5	1	101	Cheese	101 - Cheese	AZ	Phoenix	High	Josh	Rebecca	Year Built	2003	11-20	19	6/9/2019	Wind	Wind - Damage	Wind - Damage Onsite	500	$12,523	$3,250	$298	$25
6	1	101	Cheese	101 - Cheese	AZ	Phoenix	High	Josh	Rebecca	Year Built	2003	11-20	19	7/4/2021	Wind	Wind - Damage	Wind - Damage Onsite	500	$167,936	$2,356	$165,580	$336
7	1	102	Burger	102 - Burger	ME	Bangor	High	John	Rebecca	Year Built	2004	11-20	18	6/8/2019	Wind	Wind - Damage	Wind - Damage Onsite	100	$19,795	$26	$19,769	$198
8	1	103	Apple	103 - Apple	AR	City	High	Amy	Rebecca	Year Built	2005	11-20	17	12/31/2020	Wind	Wind - Damage	Wind - Damage Onsite	300	$29,797	$1,323	$28,474	$99
9	1	103	Apple	103 - Apple	AR	City	High	Amy	Rebecca	Year Built	2005	11-20	17	2/9/2021	Wind	Wind - Damage	Wind - Damage Onsite	300	$19,797	$2,326	$17,471	$66
10	1	104	Mac n Cheese	104 - Mac n Cheese	AK	Anchorage	High	Ted	Brian	Year Built	2006	11-20	16	7/9/2021	Wind	Wind - Damage	Wind - Damage Onsite	400	$205,263	$200,000	$5,263	$513
11	1	104	Mac n Cheese	104 - Mac n Cheese	AK	Anchorage	High	Ted	Brian	Year Built	2006	11-20	16	7/5/2021	Wind	Wind - Damage	Wind - Damage Onsite	400	$477,877	$2,563	$475,314	$1,195
12	1	105	Pasta	105 - Pasta	ME	Farmingdale	High	Clarissa	Brian	Year Built	2007	11-20	15	2/1/2019	Wind	Wind - Damage	Wind - Damage Onsite	200	$647,976	$12,564	$635,412	$3,240
13	1	106	Chicken	106 - Chicken	MN	Eden Prairie	High	Sharlene	Brian	Year Built	2008	11-20	14	3/19/2019	Wind	Wind - Damage	Wind - Damage Onsite	50	$136,568	$3,164	$133,404	$2,731
14	1	106	Chicken	106 - Chicken	MN	Eden Prairie	High	Sharlene	Brian	Year Built	2008	11-20	14	6/7/2022	Wind	Wind - Damage	Wind - Damage Onsite	50	$528,476	$40,945	$487,531	$10,570
15	1	107	Grapes	107 - Grapes	MN	St. Anthony	High	Jason	Brian	Year Built	2009	11-20	13	4/4/2019	Wind	Wind - Damage	Wind - Damage Onsite	75	$490,767	$43,797	$446,970	$6,544
16	1	107	Grapes	107 - Grapes	MN	St. Anthony	High	Jason	Brian	Year Built	2009	11-20	13	3/2/2021	Wind	Wind - Damage	Wind - Damage Onsite	75	$521,279	$46,650	$474,629	$6,950
17	1	108	Turkey	108 - Turkey	MN	Bloomington	High	Ben	Paul	Year Built	2010	11-20	12	6/8/2019	Wind	Wind - Damage	Wind - Damage Onsite	100	$551,791	$49,502	$502,289	$5,518
18	1	108	Turkey	108 - Turkey	MN	Bloomington	High	Ben	Paul	Year Built	2010	11-20	12	12/31/2020	Wind	Wind - Damage	Wind - Damage Onsite	100	$582,302	$52,354	$529,948	$5,823
19	1	109	Oranges	109 - Oranges	IA	Sioux City	High	Amy	Paul	Year Built	2011	11-20	11	2/9/2021	Wind	Wind - Damage	Wind - Damage Onsite	50	$612,814	$55,207	$557,607	$12,256
20	1	110	Beetroot	110 - Beetroot	TX	Houston	High	Jamie	Paul	Year Built	2012	0-10	10	7/9/2021	Wind	Wind - Damage	Wind - Damage Onsite	125	$643,326	$58,059	$585,267	$5,147
21	1	111	Pumpkin	111 - Pumpkin	TX	Beaumont	High	Phil	Ron	Year Built	2013	0-10	9	7/5/2021	Wind	Wind - Damage	Wind - Damage Onsite	175	$673,837	$60,911	$612,926	$3,850
22	1	112	Pancakes	112 - Pancakes	TX	Beaumont	High	Ben	Ron	Year Built	2014	0-10	8	2/1/2019	Wind	Wind - Damage	Wind - Damage Onsite	150	$704,349	$63,764	$640,585	$4,696
23	1	112	Pancakes	112 - Pancakes	TX	Beaumont	High	Ben	Ron	Year Built	2014	0-10	8	3/19/2019	Wind	Wind - Damage	Wind - Damage Onsite	150	$734,861	$66,616	$668,245	$4,899
24	1	112	Pancakes	112 - Pancakes	TX	Beaumont	High	Ben	Ron	Year Built	2014	0-10	8	6/7/2022	Wind	Wind - Damage	Wind - Damage Onsite	150	$765,372	$69,468	$695,904	$5,102
25	1	112	Pancakes	112 - Pancakes	TX	Beaumont	High	Ben	Ron	Year Built	2014	0-10	8	4/4/2019	Wind	Wind - Damage	Wind - Damage Onsite	150	$795,884	$72,321	$723,564	$5,306
26	1	113	Sausage	113 - Sausage	MO	St. Louis	High	Daryn	Ron	Year Built	2015	0-10	7	3/2/2021	Wind	Wind - Damage	Wind - Damage Onsite	20	$826,396	$75,173	$751,223	$41,320
27	1	114	Lemon	114 - Lemon	IL	Mt. Vernon	High	Ted	Ron	Year Built	2016	0-10	6	6/8/2019	Wind	Wind - Damage	Wind - Damage Onsite	200	$856,908	$78,025	$778,882	$4,285
28	1	114	Lemon	114 - Lemon	IL	Mt. Vernon	High	Ted	Ron	Year Built	2016	0-10	6	12/31/2020	Wind	Wind - Damage	Wind - Damage Onsite	200	$887,419	$80,878	$806,542	$4,437
29	1	115	Tomato	115 - Tomato	MO	Jennings	High	Ben	Ron	Year Built	2017	0-10	5	2/9/2021	Wind	Wind - Damage	Wind - Damage Onsite	265	$917,931	$83,730	$834,201	$3,464
DATA

Cell Formulas
Range	Formula
L3:L29	L3	=IFERROR(IF(AND($K3>='[062922 Property Claim Data.xlsm]Year Ranges'!$D$5,'[062922 Property Claim Data.xlsm]Year Ranges'!$E$5>=$K3),'[062922 Property Claim Data.xlsm]Year Ranges'!$C$5,IF(AND($K3>='[062922 Property Claim Data.xlsm]Year Ranges'!$D$6,'[062922 Property Claim Data.xlsm]Year Ranges'!$E$6>=$K3),'[062922 Property Claim Data.xlsm]Year Ranges'!$C$6,IF(AND($K3>='[062922 Property Claim Data.xlsm]Year Ranges'!$D$7,'[062922 Property Claim Data.xlsm]Year Ranges'!$E$7>=$K3),'[062922 Property Claim Data.xlsm]Year Ranges'!$C$7,IF($K3<='[062922 Property Claim Data.xlsm]Year Ranges'!$D$8,'[062922 Property Claim Data.xlsm]Year Ranges'!$C$8,NA())))),"Unknown")
M3:M29	M3	=IFERROR(YEAR(TODAY())-$K3,"Unknown")
U3:U4,U6:U29	U3	=S3-T3
V3:V29	V3	=S3/R3
D3:D29	D3	=TEXTJOIN(" - ",,B3,C3)