4 Replies Latest reply on Oct 28, 2016 12:12 PM by Derek Yuen

    Filtering blended metric by dimensions only found in primary datasource - dimension padding needed?

    Derek Yuen

      I have an urgent issue with a business dashboard related to a blended metric.


      I've attached the scenario using Superstore data (real dashboard is confidential):


      We have metrics in a primary datasource (in this case Sales), split by several dimensions (in this case Category, Customer Name, Region, Ship Mode).


      We have the same metric in a secondary datasource at a lower level of granularity - in this case just by Region.


      A calculated metric (Sales - Combined) sums Sales across both datasources.


      We must let the user filter on primary datasource dimensions not present in the secondary datasource.


      Problem: When filtering on obscure enough values (in this case just on Aaron Bergman), there are no rows in the primary datasource for certain regions. Thus, there is no "East" or "South" region on which to blend Third Party Sales. Thus, we get an inaccurate sum of total sales for those regions.


      Expected behavior: Filtering on primary-datasource-only fields will reduce the count of First Party Sales as appropriate, but Third Party Sales per region will always be factored into the sum.


      I believe the solution is to add rows to the primary datasource, such that there is at least one row for every unique combination of values under the filtered dimension(s). To make sure these dummy rows don't influence summed metrics, we fill down all metric columns with 0 for these rows.


      Can anyone please confirm that I correctly assessed both my problem's cause and its solution? Thank you very much. Of course if I am wrong or if there's a better solution, I'm all ears.


      If it matters, the real dashboard is running off SQL tables in the same database for both datasources.