Calculation over multiple tables

[Andre96]

Hi all,

My goal is to combine the results of two data tables.
I have a Data model that contains four tables at all:

• Table1: Data Table with subscription licenses for every customer for every month.
• Table2: Data Table with sales quantity for goods sold. A customer can have multiple entries per month. There is a product and a correspoding product category.
• uniqueTable: unique list of all customers.
• calender: date table for model.

My goal is to count the number of relvant car customers and sum up the number of corresponding subscription licenses.

1st step is to define relevant car customers. Relevant means that they have to buy at least 100 units of car products. This targets only Table2.
2nd step is to count the number of relevant car customers. This targets only Table2.
3rd step is to sum up the number of subscription licenses of relevant car customers. This targets Table1 and Table2.

I have already found a solution for the the first two steps (maybe a bad one, but it is working).
But for the 3rd step I haven`t found a solution so far.

Data:

Table1:

Date	Customer	Subscription Licenses
2022-01-31	001	10
2022-01-31	002	12
2022-01-31	003	15
2022-01-31	004	20
2022-02-28	001	11
2022-02-28	002	12
2022-02-28	003	17
2022-02-28	004	20
…	…	…

Table2:

Date	Customer	Product Category	Product	Amount
2022-01-31	001	House	Door	100
2022-01-31	001	House	Roof	50
2022-01-31	001	Car	Tyre	250
2022-01-31	002	Car	Tyre	40
2022-01-31	002	Car	Wheel	10
2022-02-28	001	House	Door	80
2022-02-28	001	Car	Tyre	150
2022-02-28	002	Car	Wheel	120
…	…	…	…	…

uniqueTable:

Customer

001

002

003

004

...

calender:

Date

2022-01-31

2022-02-28

…

For step 1+2 my solution that I found so far looks as follows:

step1:

relevant_Car_customer = 
	
VAR _limit = 100

VAR _salesAmount
	CALCULATE(
		SUM('Table2'[Amount]),
		FILTER('Table2'[Product Category] = "Car")
	)
	
RETURN
	IF(
		_salesAmount >= _limit,
		1,
		0
	)
	

step2:

number_of_relevant_Car_customers = 

SUMX(
	VALUES('Table2'[Customer]),
	[CAR_customer]
)

The relationships are defined as follows:

• 'Table1'[Customer] n:1 'uniqueTable'[Customer]
• 'Table2'[Customer] n:1 'uniqueTable'[Customer]
• 'Table1'[Date] n:1 'calender'[Date]
• 'Table2'[Date] n:1 'calender'[Date]

My desired solution looks as follows:

Date	number of customers	number of subscription licenses
2022-01-31	1	10
2022-02-28	2	23

Explanation:

• 2022-01-31: 1 relevant car customer: Customer 001, because sales quantity in car is 250 (>= 100) | 002 has 50 units (< 100)
  subscription licenses: 10 (Customer 001)
• 2022-02-28: 2 relevant car customer: Customer 001 and 002
  subscription licenses: 23 (11+12=23) (Customer 001+002)

Thank you very much for your help!
Best

Andre

[Andre96]

Hi @lbendlin thanks a lot for your solution. Helps me a lot!

[lbendlin · Accepted solution]

See attached.