I have been struggling with this and haven't been able to see the correct answer by searching through the forums, so I hope someone can help me as it seems simple...
I have 2 data sets that I join on 4 variables, and I want to be able to filter by these same variables:
The one data set is the full sales breakdown by part number, whereas the other shows the vehicle sales.
On a dashboard I want to show 4+ sheets that have the same underlying data but display it by specific categorizations.
Where the problem arises is when performing table calculations using the 2 data sources: sum(sales) / sum(vehicles)
The sales are correct, but the vehicles are duplicated depending on the data level, giving an incorrect result.
It is also a problem where it only calculates where one exists against the other. For example, if I had no sales one month, then it will not count the vehicles. So across a time period I will have a disparate denominator.
The only way I have been able to get accurate calculations is by showing all variables that I blended on, but that defeats the purpose. I want the data aggregated, and the calculations made at the display level.
I could also run 10+ data sources to read the data in at the different levels, but that seems like a ridiculous amount of unnecessary overhead.
I have attached a dummied down workbook that I have been playing with to try and achieve the wanted results.