I have a dashboard in Power BI that tells me all about orders and products.
Imagine you're a clothing store owner where customers can buy in a physical store, by phone and online. Store and phone orders are known as "offline channel". All products can be ordered in an "offline channel" but only specific products can be ordered online. I want to set up a visual in Power BI that helps me see:
- what the top x combinations of products are that customers buy in a single order - lets say top 10
- how many times each combination of products has been ordered
- which of those products in the combination are available in offline channels only
- which of those products in the combination are available online
e.g. i'd want an output probably like this table in a matrix visual or something:
| Products Ordered Combination | Volume | Available in offline channels only | Available online |
|---|---|---|---|
| AB1, TY2 | 5000 | AB1, TY2 | |
| AB1 | 4500 | AB1 | |
| AZ9 | 3500 | AZ9 | |
| AB1, AZ9 | 700 | AZ9 | AB1 |
| AB1, TY2, AZ9 | 50 | AZ9 | AB1, TY2 |
I just don't know how to get my raw data in Power BI into a position where it can be put into a visual. Here are the key tables and fields of data:
ORDERS Table - 1 record per order placed
ORDER_ID ORDER_CHANNEL
-------------------------------
123456 STORE
123457 STORE
123458 ONLINE
987654 PHONE
ORDERS_PRODUCTS Table - shows every product ordered as part of an order
ORDER_ID PRODUCT_ID
-------------------------------
123456 AB1
123456 ZX9
123456 TY2
123457 AB1
123458 AB1
987654 AZ9
987654 TY2
PRODUCTS Table - 1 record per product that is available for customers to order
PRODUCT_ID AVAILABLE_TO_ORDER_ONLINE
-------------------------------
AB1 Y
AZ9 N
TY2 Y