Multi Customer and Account Balance Transactions with no detail to calculate aggregated balances

tableaucommunity@tableau.com — Fri, 13 Jun 2014 05:01:52 GMT

I have what I thought would be a straight forward query however when looking at it as there is no detail in the table the query won't work. Note: Initially I need a Table.

Example:

Type	Customer	Account	Balance
Personal	Nick	1	100
Personal	Emily	1	100
Personal	Peter	2	200
Personal	Paul	3	300
Staff	Joan	3	300

Grouping at Type Level the following is achieved: (I need this as I want filter out staff customers)

Type
Personal	700.0
Staff	300.0
	1000

However as we know our account balances don't double with 2 people and the answer should be weighted/shared across the customer types but it needs to be dynamic based on the filter as if aggregate to the type, the values you see are:

Type	Balance
Personal	450
Staff	150
	600

(200/2)+(200/1)+(300/2) = 600

Then filtering out staff we should get just the overall account balances ($600)

Type	Balance
Personal	600
	600

i.e (200/2)+(200/1)+(300/1) = 600

But since we don't have the customer & account information in the query the table can't figure it out.

Am I missing something really obvious??? Please help this is doing my head in....

Thanks in advance

Nick.

Message was edited by: Nick Lupis Updated to clarify a little further.

Content in Tableau Community Forums

Multi Customer and Account Balance Transactions with no detail to calculate aggregated balances