Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
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% |
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.
Explain your requirement for Customer A
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?
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |