Hi,
I am facing a challenge which I assume has a fairly easy solution, but for some reason I am not able to find it myself.
Here is a description of the current circumstances:
We sell products to multiple customers around the world. Some orders are sent by airplanes (flight cargo), while others are sent by other means (i.e. by trucks, by boats/ships, etc.). In addition, orders have distinct salesTypes. Some orders are registered as "contract sales", while others are registered as "spot" sales, so we lable orders by different categories (i.e. 1 = fixed price contract, 2 = variable price contract, 3 = spot, etc.). A customer can have many or no contracts, i.e. some customers only purchase goods via the spot market, others can have multiple contracts for different products and also purchase some goods via the spot market. Each customer also has a Boolean value to specify whether or not shipping must be done via airplanes (flightCargo) or not. For the contracts, each contract has a weekly delivery schedule which specifies the volume and the price for the deliveries agreed upon in the contract.
Given these circumstances, my objective is as follows:
For a specified period (date interval), list the following (split by customer and contract (in case of an existing contract id)) for all customers who has a positive (i.e. larger than 0) scheduled contract delivery volume within the period:
- Sum of planned delivery volume (must be >0) and shipping type = flight
- Sum of volume sold as a "contract sale" and shipped by flight (sum of sales registered as a contract sale (split by customer and contract id))
- Sum of volume sold as a "spot sale" and shipped by flight.
The goal is to identify the split between contract sales and spot sales for all "flight customers" for a given period, under the condition that only customers who are supposed to receive contract volumes in the period should be evaluated.
I have the following tables:
Customer
id (PK)
name
flightCargo (boolean)
Sales
id (PK)
customerId (FK) (relates to id in Customer table)
contractId (FK) (relates to id in Contract table, empty if salesType != contract)
date
salesType (contract, spot, etc.)
volume
price
Contract
id (PK)
contractName
startDate
endDate
customerName
customerId (is the same id's as used in the Customer table, but no direct relationship is active between the tables as this causes a cyclical reference (already existing reference via Sales table)
ContractDeliverySchedule
id (PK)
contractId (FK) (relates to id in Contract table)
deliveryWeek
volume
price
I want something like this:
| | Planned Contract Volume | Sold Contract Volume | Sold Spot Volume | Total Sold Volume |
| Customer A | 100 | 105 | 20 | 125 |
| - Contract A.1 | 50 | 55 | | 55 |
| - Contract A.2 | 50 | 50 | | 50 |
| Customer B | 40 | 40 | 32 | 72 |
| - Contract B.1 | 40 | 40 | | 40 |
With my current setup, if I use customerName and contractName from the Contract Table as a row in PBI, I get the right calculated weekly contract volume split by customer and contractName. I also get the right "Sold Contract Volume", but the "Sold Spot Volume" is not split correctly by customerName (as the salesTable has "NAN" for contractId in its sales column.
If I instead use "customerName" from the Customer Table to split by rows, I get the right "Sold contract volume", right "Sold Spot Volume", but the "Planned Contract Volume" is the same across all customers (not split correctly).
How can I go about fixing this?
Regards
