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.
Hello DAX Community
Our company uses a 3rd Party Service to distribute samples to clients, and we are looking to measure its impact on sales. For the sake of simplicity, I've created some tables that showcase the type of data I have to work with.
The first is data that we receive from the 3rd Party Service
Date | Company | SKU | Samples Requested |
1/7/2020 | A | 4333-12 | 1 |
2/3/2020 | B | 1659-01 | 1 |
3/14/2020 | A | 2201-03 | 1 |
3/16/2020 | C | 4333-10 | 1 |
4/2/2020 | A | 1730-06 | 1 |
4/22/2020 | B | 7740-04 | 1 |
5/13/2020 | A | 6770-15 | 1 |
5/16/2020 | D | 6550-11 | 1 |
5/24/2020 | C | 5433-07 | 1 |
The second table is our company's sales table:
Date | Company | SKU | Sales Amount |
2/7/2020 | C | 2244-06 | 350.00 |
2/16/2020 | B | 1659-01 | 1600.00 |
3/20/2020 | A | 2201-03 | 2730.00 |
4/5/2020 | A | 9954-02 | 880.00 |
4/16/2020 | D | 8740-10 | 170.00 |
5/1/2020 | B | 3579-08 | 5520.00 |
5/3/2020 | A | 2603-22 | 800.00 |
5/22/2020 | C | 4333-10 | 640.00 |
5/30/2020 | B | 8879-04 | 3250.00 |
6/5/2020 | A | 4333-12 | 120.00 |
6/6/2020 | D | 6550-11 | 1100.00 |
6/24/2020 | A | 1730-06 | 990.00 |
What I would like to determine is which companies are purchasing after they receive a corresponding sample from the sample service, as well as the sales volume as a result. I find this calculation quite difficult since there are 3 relationships that need to be accounted for between the two tables: Date, Company, and SKU.
I've pondered for a few hours on how to go about writing the DAX, but am stuck on which level to start at. Any assistance or tips would be greatly appreciated.
Hi @achentrp ,
Based on your description and data sample, not certain what is your expected output. If wants to match the company name, date and SKU at the same time, seems like there is no data in the sample which meets all the requirements.
Could you please consider sharing more details about this issue for further discussion?
Best Regards,
Yingjie Li
Hello @achentrp, just to confirm, I do not know if the columns are well aligned, but the first row of the table the first table would be Company A, Sku s 433-12, requested samples s 1
SKU is 4333-12, everything else is correct.
Yea, don't know why when I input the table the alignment is all off....
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |