0 Replies Latest reply on Jun 21, 2013 8:48 AM by Janice Hibbert

    Data Blending duplicates and/or excludes data except at the lowest level

    Janice Hibbert

      HI

       

      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:

      1. Year
      2. Month
      3. Country
      4. Vehicle

       

      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.

       

      I thank you for your assistance

       

      Cheers

      Janice