cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
s1107982
Frequent Visitor

Help with Product combinations

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!

1 REPLY 1
v-easonf-msft
Community Support
Community Support

Hi,  @s1107982 

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

11.png

 

 

Best Regards,
Community Support Team _ Eason

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.