Help with Product combinations

[Anonymous]

Hi,

I'm new with DAX and I have a question.

I need the product combinations of the orders. I have an order table, an orderline table and a product table.

What I want is to count the product combinations of an order, also with the order price. I want 2 tables as below.

The relations between my data are index from order to orderline and productID in orderline to product. 

The tables with columns I have = 

Order

Index	Orderamount
1	17.00
2	5.00
3	14.50
4	7.50
5	7.00
6	11.00
7	11.00

Orderline

Index	Quantity	ProductiD
1	1	20165
1	2	20166
2	1	20165
3	2	20172
3	1	20173
3	1	20174
4	1	20181
4	1	20182
5	2	20182
6	1	20165
6	1	20166
7	1	20165
7	1	20166

Product

ProductId	ProductName	Price
20165	Sun Spray	5.00
20166	After Sun	6.00
20172	BBQ Time Instant	4.00
20173	FireLighters	4.00
20174	Water	2.50
20181	LED Light	4.00
20182	Food Storage Set	3.50

What I want in my dashboard:

A table with the product combinations and the count of orders with this product combination.

Product combination	Count orders
SunSpray & Aftersun	3
Sun Spray	1
BBQ Time Instant & FireLighters & Water	1
LED Light & Food Storage Set	1
Food Storage Set	1

And a table with the total amount of the product combinations and the count of the orders with this combination. A customer can order multiple same products, the combination order remains te same and the price is going up. 

So what I want:

Amount order combination	Product combination	Count orders
11.00	SunSpray & Aftersun	2
17.00	SunSpray & Aftersun	1
5.00	Sun Spray	1
14.50	BBQ Time Instant & FireLighters & Water	1
7.50	LED Light & Food Storage Set	1
7.00	Food Storage Set	1

I hope someone can help me.
Thanks in advance!

[v-easonf-msft]

Hi,  @Anonymous 

Not very clear.Can you explain in more details?

How did you create the "Product Portfolio" column?Why does the value "SunSpray" exist in the expected table but the value "aftersun" does not exist?

Before creating report, you can  consider using function' lookupvalue' to integrate data from multiple tables into table 'Orderline'  first.

calculated columns:

Orderamount = LOOKUPVALUE('Order'[Orderamount],'Order'[Index],Orderline[Index])

Product = LOOKUPVALUE('Product'[ProductName],'Product'[ProductId],Orderline[ProductiD])

Price = LOOKUPVALUE('Product'[Price],'Product'[ProductId],Orderline[ProductiD]) 

Best Regards,
Community Support Team _ Eason