Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Giorgi1989
Advocate II
Advocate II

Pulling 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 1Fact Table 1Fact Table 1Fact Table 2Fact Table 3
CustomerSales 2019Sales 2020Sales 2021Bonuses 2021Forecast 2023
A500500500200700
B400400400200600
C300300300200800
Etc.200200200200600
1 ACCEPTED SOLUTION
johnt75
Super User
Super 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]) ) 

View solution in original post

3 REPLIES 3
Giorgi1989
Advocate II
Advocate II

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:

 

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: 
 
Giorgi1989_0-1658839527000.png
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!

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.

johnt75
Super User
Super 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]) ) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors