How to build up a matrix with contents from different table

[Anonymous]

Hi all,

Below is how my raw table look like:

Table 1					Table 3
Customer	Date	Bonus type	Amount		Customer	Date	Purchase Amount
A	5/1/2019	Discount 5%	50		A	5/1/2019	1000
A	5/2/2019	Rebate	100		A	4/30/2019	500
A	4/30/2019	New Join	5		B	5/3/2019	500
B	5/1/2019	Rebate	20		B	4/29/2019	100
B	4/29/2019	Discount 10%	10		C	5/9/2019	50
C	5/2/2019	Rebate	3		C	4/30/2019	300
Table 2
Customer	Date	Bonus type	Amount
A	4/30/2019	Sign up	2
B	5/4/2019	Reload bonus	5
B	4/29/2019	Sign up	2
C	5/5/2019	Reload bonus	5
C	5/3/2019	Friends	10

I would like to get a visualisation for May as below:

If bonus/purchase > 10% then i would like to know the details of their bonus apply.

Customer	Purchase Amount	Total bonus amount	Bonus/Purchase	Rebate	Discount	Reload bonus	Friends
A	1000	150	15.0%	100	50	0	0
B	500	25	5.0%	20	0	5	0
C	50	8	16.0%	3	0	5	10

 What i did was use measure to calculate sum of each bonus type. However, the type of bonus was too many (>20) and will be adding new type from time to time. Thus, i would like to know, is there any simpler way to have this done? (If possible, i will not want the table to be appended as it will crash due to the huge data size)

[Arjunarao]

If I calculate the % this was the output.

Which records you want to pick?

Customer	Bonus type	Bonus Amt	Purchase Amt	Bonus Amt/Purchase Amt %
A	Discount 5%	50	1500	3.33%
A	New Join	5	1500	0.33%
A	Rebate	100	1500	6.67%
A	Sign up	2	1500	0.13%
B	Discount 10%	10	600	1.67%
B	Rebate	20	600	3.33%
B	Reload bonus	5	600	0.83%
B	Sign up	2	600	0.33%
C	Friends	10	350	2.86%
C	Rebate	3	350	0.86%
C	Reload bonus	5	350	1.43%

[Anonymous]

@Arjunarao 

the bonus/purchase amount should be total bonus/total purchase amount. For the case of customer A, it should be 150/1500 = 15%.  To get the total bonus of customer A in May, I have to add up the 50 from Discount 5% and 100 from rebate in Table 1. (If for Customer C, then you will have to add up the bonus in Table 1 and Table 2). If this ratio of a customer is > 10%, then i would like it to be shown in my visualisation in the format that i showed previously.

sorry for making you confuse.

[Arjunarao]

Explain your requirement for Customer A

[Arjunarao]

Hi, Can you elaborate "If bonus/purchase > 10% then i would like to know the details of their bonus apply" with an example of a customer?

[Anonymous]

@Arjunarao,

This is the visualisation result that i wish to have.

Customer	Purchase Amount	Total bonus amount	Bonus/Purchase	Rebate	Discount	Reload bonus	Friends
A	1000	150	15.0%	100	50	0	0
C	50	8	16.0%	3	0	5	10