Distinct Count - Multiple Groups - Grand Total is off if duplicate in column's value

[rmg1]

Please see attached for an example of a problem I am having in reporting the Grand Total when using Distinct Count.

I have 3 groups of which Group 2 has distinct count on group 3 (quote numbers) and Group 1 should be the total of all Group 2 distinct counts.

I am also doing a distinct count in GF1 by using a summary on distinct count of Group 3 - Quote numbers.

Group 1 and Group 2 totals are correct.

The problem is in RF (Grand Total). I expect to see the total of each Group 1 for each column.

Please see attached since I am not sure how best to explain. The example is based on just one column, "EQ", for easier reading of sample.

If you have any questions, please ask.

 

[Charliy]

The total of a Distinct Count is not always the sum of the subtotals, that's the wa it's supposed to work.

For example let's say you're counting visitors per day.

On Monday you had Tom, Dick, and Harry- 3
On Tuesday you had Harry, Ron, and Hermione- 3

Your grand total is 5, because Harry is only counted once.

 

[rmg1]

I understand that. How do I get the correct grand totals to not count duplicates in other groups?

 

[rmg1]

Maybe this will aid in helping me solve the issue:

Distinct Count on each Group 2 with Grand Total (must equal sum of all Abc line totals)

				Group 3	
Group 1	Group 2	Invoice#	Invoice Line	Running Total for 	Expect for Abc
Distinct Count Grp 2	Grand Total


Apples		Abc		1		1		1
Apples		Abc		2		1		2
Apples		Abc		2		2		2
						Total for Abc = 2			2

Apples		Bcd		3		1		1
						Total for Bcd = 1
Total for Apples  = 3

Bananas	Abc		4		1		1
						Total for Abc = 1			1

Bananas	Bcd		4		2		1
						Total for Bcd = 1
						Total for Bananas = 2

Oranges	Abc		2		3		1
Oranges	Abc		3		2		2
Oranges	Abc		5		1		3
Oranges	Abc		5		2		3
Oranges	Abc		6		1		4
						Total for Abc = 4			4

Oranges	Bcd		3		3		1
						Total for Bcd = 1

Oranges	Cde		4		1
						Total for Cde = 1
						Total for Oranges = 6

									Expect Abc Total to be = 7

 

[Charliy]

What total are you getting? What field are you counting?

When working with distinct counts I normally create a formula that combines a few fields and count that.

{field1}&' '&{field2} It looks like you need to count group1 combined with invoice.

Always put a separater between the concatenated fields. With my data I have a patient id and an episode number. If I didn't put a seperater between them then Patient 491 episode 2 would look the same as patient 49 episode 12.

 

[rmg1]

In response:

What total are you getting? I am getting a grand total of 6 for Abc total. This should be 7.
What field are you counting? In above example, I am counting the number of unique invoices in Group3 which are in Group 2.
I need to show all of the totals for Group 2 in Group Footer 2
GF1 is to sow the total counts of all GF2 totals. So far, this has not been a problem.
RF - totals for all GF1 lines are accumulated here.
If any a column in Group2 has a duplicate invoice, it does not count it. This is incorrect.
Is it safe to say that I need a count of all GF2 distinct counts?

Maybe looking at the attached report will shed a new light. It uses different examples.
I used color to emphasize areas.

 

[Charliy]

The what yo uneed to do is a distinct count on a concatenated field.

Instead of counting "1", you need to count either "abc 1" or "Apples abc 1".

 

[rmg1]

Thank you for the help.