Expression

[Anonymous]

Hi Experts

I want to calculate sales amount based on category and broker name, the difficulty is that every sales number maybe allocate to more than one people, how to calculate sales amount based on category and broker name?(excel file is attached)

my tables are?

     Table1:

Sales number	Date	Broker Name	Percent
1	4/5/2017 0:00	A	50
1	4/5/2017 0:00	B	50
2	3/25/2018 0:00	B	100
3	8/4/2016 0:00	A	60
3	8/4/2016 0:00	C	40

Table2:

ItemCode	Sales_Quantity	Sales_NetPrice	Sales_Number	Date	SubCatgoriyes
3001	100	22000000	3	8/4/2016 0:00	Cover
3002	100	22000000	3	8/4/2016 0:00	Cover
3003	100	22000000	3	8/4/2016 0:00	Cover
3004	100	13500000	3	8/4/2016 0:00	Cover
3005	100	8000000	3	8/4/2016 0:00	Cover
4001	100	22000000	3	8/4/2016 0:00	Cover
4002	100	22000000	3	8/4/2016 0:00	Cover
4003	100	22000000	3	8/4/2016 0:00	Cover
4004	100	13500000	3	8/4/2016 0:00	Cover
4005	100	8000000	3	8/4/2016 0:00	Neck Back
5001	100	8000000	3	8/4/2016 0:00	Neck Back
5002	100	9000000	3	8/4/2016 0:00	Neck Back
5003	100	13500000	3	8/4/2016 0:00	Neck Back
5004	100	19000000	3	8/4/2016 0:00	Neck Back
5005	100	19000000	3	8/4/2016 0:00	Neck Back
5006	100	19000000	3	8/4/2016 0:00	Neck Back
5007	100	19000000	3	8/4/2016 0:00	Neck Back
9024	3	10044000	1	4/5/2017 0:00	Neck Back
9005	1	4092000	1	4/5/2017 0:00	Neck Back
9007	1	4092000	1	4/5/2017 0:00	Cover
9008	10	14415000	1	4/5/2017 0:00	Cover
9026	10	25110000	1	4/5/2017 0:00	Neck Back
9027	5	12555000	1	4/5/2017 0:00	Neck Back
9001	20	64170000	1	4/5/2017 0:00	Neck Back
9003	5	16042500	1	4/5/2017 0:00	Neck Back
9023	10	33480000	1	4/5/2017 0:00	Neck Back
9025	2	6696000	1	4/5/2017 0:00	Neck Back
30002	300	157500000	2	3/25/2018 0:00	Cover
30003	150	78750000	2	3/25/2018 0:00	Phone
30020	60	119400000	2	3/25/2018 0:00	Phone
30021	60	119400000	2	3/25/2018 0:00	Phone

the answer:

Row Labels	Sum of Sales_NetPrice	A	B	C	A	B	C
Cover	343,007,000
1	18,507,000	50%	50%	0%	9,253,500	9,253,500	-
2	157,500,000	0%	100%	0%	-	157,500,000	-
3	167,000,000	60%	0%	40%	100,200,000	-	66,800,000
Neck Back	286,689,500				-	-	-
1	172,189,500	50%	50%	0%	86,094,750	86,094,750	-
3	114,500,000	60%	0%	40%	68,700,000	-	45,800,000
Phone	317,550,000				-	-	-
2	317,550,000	0%	100%	0%	-	317,550,000	-
Grand Total	947,246,500				264,248,250	570,398,250	112,600,000

[Lisa_P]

Load both tables and make sure they are associated on Sales Number - must be exactly the same spelling and case for each table.

You can achieve this by using the expression: Sum(Sales_NetPrice)*Percent/100

When you put this in a pivot table, similar to your answer