Average accounts in household when one of the account types is checking

[lbunnell]

HH #	Acct #	Account Type
1234	0123	Checking
1234	0234	Savings
1234	0345	Money Market
2345	0456	Savings
2345	0567	Checking
3456	0678	Checking
3456	0789	Savings
...

For a table like the one above, I'm looking to create a chart with an expression that indicates the average number of accounts within a household when the household has a checking account.

Something like:

if([Account Type]='Checking', count(distinct [Acct #]) / count( distinct [HH#]))

but I believe this expression this would only count the checking accounts per household. I need the average of the total number of accounts in each household and I need to aggregate in some way for a chart.

[Anonymous]

could you please elaborate a bit more, please create an example with households with and without 'Checking' account and tell what result you expect.

[lbunnell]

Robin,

To clarify, in the above example, HH #1234 has 3 accounts, 1 of which is checking. HH #2345 has 2 accounts, 1 of which is checking. HH #3456 has 2 accounts 1 of which is checking. I'd like to show, for example, that households (HH) that have a checking account on average have a total of 2 accounts.

Thanks,

Lawrence

[Anonymous]

sorry, perhaps I'm a bit slow on the uptake today , I still don't understand you requirement