Data Model for Pivot table

[Anonymous]

I have data coming in the following structure.

Customer	Territory	Product	Product Sub Category	Sales	Opportunity
Cust1	120	A	A1	$150	300
Cust2	130	A	A1	$0	300
Cust3	120	A	A2	$100	300

and would like to show the following tables in the dashboard.   When the user selects "A1" from Object 1, I'd like show both the cust 1 and cust 2 in the straight table.  My question is - is it good idea to create a temp table in the Pivot table form while loading the data itself or is there a why we can achevie this just with the base table ?

Pivot Table Object 1.

Product	Product Sub Category	Sales	Total Opportunity	Mkt share
A	A1	$150	900	$0.17
A	A2	$100	900	$0.11

Straight Table Object 2

Customer	Territory	Product	Product Sub Category	Sales	Opportunity
Cust1	120	A	A1	$150	300
Cust2	130	A	A1	$0	300
Cust3	120	A	A2	$100	300

Thanks !

[#2]

Hi quser_learn,

     I found no problem selecting A1 and seeing Cust2 in the second object. I think I don't understand your question:

[Anonymous]

Yeah ..question is how to show the market share as 0.17 and opportunity as 900 as opposed to 1 in object 1

[Anonymous · Accepted solution]

I used set analysis and conditional statements and solved it.