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!
