Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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]) )
Hi 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:
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.
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]) )
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |