2 Replies Latest reply on May 5, 2018 1:40 PM by Okechukwu Ossai

    Using LOD with Dimensions in different hierarchies

    Dan Findley

      I'm using some healthcare data that I can't share, so I'm hoping this explanation makes sense.


      I have a hierarchy with 4 tiers, and a last item that exists outside the hierarchy. There are three worksheets in my dashboard.


      This is the hierarchy:

           Tier 4: Parent

           Tier 3: Function

           Tier 2: Type

           Tier 1: Center


      And this exists outside the hierarchy: Driver


      I'm trying to figure out how to have a table that shows only the Driver and the Cost measure, where one column shows me the cost when I've filtered down to the Center, and the other shows the cost limited to the Type.


      So, it'd look like this:


      Driver:Center CostType Cost
      Band-aids Used100150
      E-mails Checked1.354.27
      Shoes Polished832645


      The issue I'm facing now is that because Driver exists outside the hierarchy, this calculation:


      {FIXED [Type], [Driver]: Average([Cost])}


      Fixes the Driver average cost across the organization, instead of the driver cost within the filtered hierarchy.


      So basically, I think the formula above locks the driver cost at the wrong tier, because some drivers exist outside the selection.


      I've used this as well:


      {EXCLUDE [CENTER]: Average([Cost])}


      This achieves the desired result, but I have to show the Type and Center in the table, and if the user filters beyond the Type, the formula shows the same cost for both columns.


      TypeCenterDriverCenter CostType Cost
      CancerCancer 1Shoes Polished135


      CancerCancer 1Band Aids Used4.276.38
      CancerCancer 2Shoes Polished954544
      CancerCancer 2Band Aids Used8.426.38
      CancerCancer 2E-Mails Checked1.354.27


      So, this works with the EXCLUDE formula, but I'd like for the table to only show the one Center and still show correct measures. Filters are happening with dashboard actions.


      If this doesn't make sense or you need more clarification, please ask! Thank you!