blending the data is equivalent to matching every record in one file with each record in the second file based on how you connect the 2 files together -
if you are not connected on date but are connected on warehouse it is will take the data in the primary source( pallet count) at each warehouse and match it with each record in the secondary source (cost) at the connection level (warehouse)
when you connect to both date and warehouse - not only those records that match both criteria will be in the total (it is similar to a sumifs() with multiple conditions in excel)
The issue is created by blended aggregated data with disaggregated data - essentially you repeat the aggregated data (cost) for each record in the disaggregate file - you can do that but you have to take into account the duplicate record in your aggregation as you create the viz
you second workbook could not be opened
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Apologies. The file I'm having trouble with is the one you couldn't open. I've uploaded a new version of the file that you should be able to open.
Try clicking and unclicking the Linking Field icon next to PlanDeliveryDate and you'll see enabling the link results in no data and disabling the link results in the pallet count of 396,726 (total pallet count for entire data set) every month rather than the pallet count by month.
The "Dummy Data" file is only there to help show what I'm trying to do and that it should work.
I think I fixed the issue in the attached workbook. In order to make it work, you need to maintain same data type of your date field. It was showing blank because one type was date and another was date-time.
Let me know if this helps or not.
CostPerPallet 2_v10.4.twbx 3.3 MB