Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |