1 Reply Latest reply on Jul 25, 2018 9:59 AM by matthew.k

    Grouping a dimension from primary (cube) source, by a measure in the primary DS, with the linked secondary DS measure


      I appreciate the title is probably confusing, it is complex to explain as a title, but the end goal is quite simple really.


      So I have a list of specific Items coming from the cube source, a list of measures (revenue in this example) and a product category from the secondary source which is linked to the Items in the primary source, which is a cube.



      Item -  a string dimension from the primary cube source, this is a specific product, it is what appears on the sales invoice, e.g. steel chair, walnut table, ...

      Product - a string dimension from the secondary (excel file) data source, there is an 'Item' field in the secondary source which maps to a 'Product' field, these are in a hierarchy (ProdCat: Product>>ItemCode, ItemCode links to Item)  this is a way to categorise the each Item, e.g. 'Furniture'

      Revenue - an integer value measure in the primary source, each Item will have a revenue for a given time period, e.g. Steel Chair - £14,000 in 2018 Q1


      What I'm trying to do is fairly straight forward.


      Since working with blended data requires both linked dimensions to be in view, I always seem to end up with all the individual items being displayed, when all I really care about is the grand total for each Product category, i.e sum the revenue for all the Items in Product = Furniture.



      So in the image above, instead of having a Furniture bar of stacked Items, there would be just one bar of the same size for Furniture.


      In the example above no information is lost, you still get to see that Furniture generates most revenue, however it becomes a massive issue when using time series graphs, as you end up with a line over time for each item, when really only one line to represent the Product category is wanted.


      Any advice much appreciated, please ask if further information is needed.