Show data but don't include in total calculations

[saanah2019]

Hey guys, I am at the end of my report but just have that one last hurdle. So I have a table Billing with fields Id, Insurance, Charge, Date

Id	insurance	charge	date
100	aetna	100	1/15/2000
100	aetna	100	1/16/2000
100	fidelis	100	1/25/2000
101	healthfirst	200	2/1/2000
101	healthfirst	200	2/5/2000
102	healthfirst	3000	3/1/2000
102	aetna	300	3/1/2000
103	aetna	400	1/15/2000
103	aetna	400	1/18/2000
103	aetna	400	1/21/2000
103	aetna	400	2/15/2000
103	aetna	400	2/18/2000
103	healthfirst	400	2/21/2000

so if want to filter on a specific id i should get :

filter on 100
Id	insurance	charge	date
100	aetna	100	1/15/2000
100	aetna	100	1/16/2000
Total		100

filter on 103
103	aetna	400	1/15/2000
103	aetna	400	1/18/2000
103	aetna	400	1/21/2000
103	aetna	400	2/15/2000
103	aetna	400	2/18/2000
103	healthfirst	400	2/21/2000
total		800

so basically this is an AR report and I want to show who I billed to and for how much but if the insurance name repeats, then obviously not to add all the occruences of one insurance  but rahter just add each distinct insurance name once. So i might just have one insurance name, or two and they might repeat like 6-8 times but I just want to subtotal each once. So my final subtotals should be  

Id	insurance	charge	date
100	aetna	100	1/15/2000
100	aetna	100	1/16/2000
100	fidelis	100	1/25/2000
101	healthfirst	200	2/1/2000
101	healthfirst	200	2/5/2000
102	healthfirst	3000	3/1/2000
102	aetna	300	3/1/2000
103	aetna	400	1/15/2000
103	aetna	400	1/18/2000
103	aetna	400	1/21/2000
103	aetna	400	2/15/2000
103	aetna	400	2/18/2000
103	healthfirst	400	2/21/2000
total		1700

Can you please advise on the best way to go about this?? Thank you. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

This will give the correct totals based on unique ID and Insurance (not date)

Total of Unique = 
SUMX( 
    ADDCOLUMNS( 
        SUMMARIZE( Table1, Table1[ID],Table1[insurance]),
        "Total Charge",
        DIVIDE( [Total Charge], CALCULATE( COUNTROWS(Table1)))
    ),
    [Total Charge]
)

[saanah2019]

@Anonymous Hey thanks for the quick answer. I get an error, "A circular dependency was detected: Billing[Total of Unique]"

[parry2k · Accepted solution]

@saanah2019 here are couple of options

option 1, add measure

Total = 
SUMX(
    DISTINCT(SELECTCOLUMNS(Table2, "Id", Table2[Id], "Insurance", Table2[insurance] ) ), 
    CALCULATE(MAX( Table2[charge]) )
)   

Option 2, add unique column and then add measure

Unique Column = CONCATENATE(FORMAT(Table2[Id],"#") ,Table2[insurance])

Total = 
SUMX(
    VALUES( Table2[Unique Column] ), 
    CALCULATE(MAX( Table2[charge]) )
)