Forum Discussion
Help needed with retrieving data from multiple fact tables for top customers
Dear All,
I need to derive a list of top 20 customers based on the actual sales (Fact Table 1), and then return different numbers for those twenty customers from multiple fact tables (these are 3 different cubes from SAP Business Warehouse).
So the output I want to arrive at eventually is something similar to the table shown below:
Please, note that in the data model there are no direct relationships among these fact tables.
Any suggestion as to how to get the desired output? Thank you in advance.
| Fact Table 1 | Fact Table 1 | Fact Table 1 | Fact Table 2 | Fact Table 3 | |
| Customer | Sales 2019 | Sales 2020 | Sales 2021 | Bonuses 2021 | Forecast 2023 |
| A | 500 | 600 | 700 | 800 | 900 |
| B | 750 | 850 | 950 | 1050 | 1150 |
| C | 1000 | 1000 | 1000 | 1000 | 1000 |
| D | 20 | 40 | 60 | 80 | 100 |
| E | 15 | 25 | 35 | 45 | 55 |
| ETC. | 12 | 13 | 14 | 15 | 16 |
1 Reply
Hi Giorgi1989
Thanks for reaching out to us.
>> I need to derive a list of top 20 customers based on the actual sales (Fact Table 1), and then return different numbers for those twenty customers from multiple fact tables
you can first filter out the top 20 customers in Power Query by filtering the sales of each customer, then use Merge.
For example, to filter top 3 sales first,
then Merge
Finally expand table,
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
