Forum Discussion
Advocate IIPulling data in from multiple fact tables for Top 20 customers
Hi,
I am trying to retrieve a list of Top 20 customers based on the actual sales, and then capture the figures from three different fact tables for these 20 customers (these are three separate cubes from SAP BW).
The output I am looking for is shown below:
Please, note that in the data model there are no direct relationships between the fact tables.
Any tip/suggestion would be much appreciated. Thank you.
| 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 | 500 | 500 | 200 | 700 |
| B | 400 | 400 | 400 | 200 | 600 |
| C | 300 | 300 | 300 | 200 | 800 |
| Etc. | 200 | 200 | 200 | 200 | 600 |
If your fact tables share a common column then you could use TREATAS. All 3 tables don't necessarily have to have the same shared column, as long as fact table 1 can be related to both fact table 2 and 3.
Bonuses 2021 = CALCULATE( SUM('Fact table 2'[bonus]), TREATAS( VALUES('Fact table 1'[Customer ID 1]), 'Fact table 2'[Customer]) ) Forecast 2023 = CALCULATE( SUM('Fact table 3'[forecast]), TREATAS( VALUES('Fact table 1'[Customer ID 2]), 'Fact table 3'[Customer]) )
3 Replies
johnt75Super User
If your fact tables share a common column then you could use TREATAS. All 3 tables don't necessarily have to have the same shared column, as long as fact table 1 can be related to both fact table 2 and 3.
Bonuses 2021 = CALCULATE( SUM('Fact table 2'[bonus]), TREATAS( VALUES('Fact table 1'[Customer ID 1]), 'Fact table 2'[Customer]) ) Forecast 2023 = CALCULATE( SUM('Fact table 3'[forecast]), TREATAS( VALUES('Fact table 1'[Customer ID 2]), 'Fact table 3'[Customer]) )
Giorgi1989Hi John, thank you for so prompt a reply. One of the problems that I have encountered is to plot my values correctly on the plot chart. I use 'Year' on the X-Axis, and the 'Values' on the Y-Axis. The 'Values' measure consists of the following formulae:
Values = SWITCH(SELECTEDVALUE(Dates[Year]),"2019",[Sales 2019],"2020",[Sales 2020],"2021",[Sales 2021],"Bonuses 22", Calculate([Bonuses 22], ALL(Dates),'Forecast 23", Calculate([Forecast 23], ALL(Dates)))Things go wrong at the 'Forecast 23', whereupon I get the following error message:Forecast 23 = Calculate(sum('Sales forecast '[Forecast 23]), TREATAS(VALUES('Sales Report' (2019-2022)'[Customer]), 'Sales forecast'[Customer]))Any thoughts on this? Many thanks again!- johnt75
It could be complaining because the "Bonus 2022" and "Forecast 23" values don't appear in the 'Dates'[Year] column. You could look into the new fields parameter feature as an alternative way to choose what is shown on the visual.