I have 3 sample datasets - Sales, ShowroomStock & Modelreference that are related to Car sales & stocks.
Sales - Contains the daily transactions done in the showrooms. (Related to ShowroomStock file by 'Showroom' & 'StockKey' and this combo is not unique)
ShowroomStock - Contains the details of stock including the stock remaining ('Showroom' & 'StockKey' together is the Primary Key)(Related to Modelreference file by 'RefName')
Modelreference - Contains the details of the Car models ('RefName' is the Primary Key)
Now, the requirement is to build a worksheet which shows the 'Stock Left' and 'Num of Cars Sold' bar chart for a particular 'Brand' of Car as shown in the 'Cars.png' snapshot.
But, the problem with that is that the StockLeft numbers are not correct and getting added up due to non-uniqueness(duplicate combo of Showroom & StockKey) of the 'Sales' data.
Ideally, my StockLeft numbers should like the ones in 'Cars_SalesIgnored.png' snapshot; I achieved this by removing the 'Sales.csv' from the data source.
Is there any way to achieve this without removing the driving table 'Sales.csv' from the data source (as I need to show the 'StockLeft' & 'NumofCarsSold' in the same worksheet). Also, I can't use Data Blending as the real-time data is very huge and performance is a constraint for me.
PFA, the attached workbooks & screenshots for reference and let me know any solutions.