Help needed to retrieve data for Top 20 customers from multiple fact tables

[Giorgi1989]

Dear All,

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	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

[Anonymous · Accepted solution]

Hi  @Giorgi1989 ,

Here are the steps you can follow：

1. Create measure.

Bonuses 2021 =
CALCULATE(SUM('Fact Table2'[Bonuses 2021]),FILTER(ALL('Fact Table2'),'Fact Table2'[Customer]=MAX('Fact Table1'[Customer])))

Forecast 2023 =
CALCULATE(SUM('Fact Table3'[Forecast 2023]),FILTER(ALL('Fact Table3'),'Fact Table3'[Customer]=MAX('Fact Table1'[Customer])))

Sum =
SUMX(FILTER(ALL('Fact Table1'),
'Fact Table1'[Customer]=MAX('Fact Table1'[Customer])),[Sales 2019]+[Sales 2020]+[Sales 2021]+[Bonuses 2021]+[Forecast 2023])

Rank =
RANKX(ALL('Fact Table1'),[Sum],,DESC)

Flag =
IF(
    [Rank]<=3,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly