Hi all,
I am not able to find an answer on the following problem i am facing.
The model has a customer table, offer table, offer items and quantity table
Then i also have an order table with an items and quantity table.
Offer is linked to customer and so is the order. Offer item is linked to offer and order item is linked to order.
There is no link between order and offer, a customer can make one order combining multiple offers.
What i try to achieve is per customer to check the offers
During this check i need to check per item the amount that was offered and compare this to orders made by the same customer.
So all offered items combined and compare to all ordered items combine for this customer (could be a positive or negative delta).
The outcome should be a Order equal offer Order not equal offer.
There is no option to link orders and offers directly then via customer. Solution can be a calculated column to any of the tables or a dax measure
Below should give a Customer 1 All Ordered, Customer 2 not all ordered
My setup is the following.
I have a table with all Offers with a related table of all individual items of the offer.
| Offernumber | Offer name | Customer number |
C1-1 | Name1 | Customer1 |
C1-2 | Name2 | Customer1 |
C1-3 | Name3 | Customer1 |
C2-1 | Name 4 | Customer2 |
| Offernumber | Item | Amount |
C1-1 | Item1 | 1 |
| C1-1 | Item2 | 10 |
| C1-1 | Item3 | 30 |
| C1-2 | Item1 | 1 |
| C1-2 | Item2 | 10 |
| C1-3 | Item1 | 1 |
| C1-2 | Item2 | 10 |
| C1-2 | Item3 | 30 |
| C2-1 | Item2 | 10 |
the order part is
| Order number | Order name | Customer number |
C1-O1 | Name1 | Customer1 |
C1-O2 | Name2 | Customer1 |
C2-O1 | Name2 | Customer2 |
| Ordernumber | Item | Amount |
C1-O1 | Item1 | 1 |
| C1-O1 | Item2 | 30 |
| C1-O2 | Item3 | 60 |
| C1-O3 | Item1 | 2 |
| C2-O1 | Item2 | 5 |
Any ideas
Thanks
RJ
